Make PostgreSQL `selectSource` stream results. #726

naushadh · 2017-10-30T01:52:48Z

Address #657 for persistent-postgres.

With this, persistent effectively will support streaming results when using selectSource for all* sql backends.

*MySQL streaming is available in persistent-mysql-haskell backend.

naushadh · 2017-10-30T01:54:56Z

Ran into some test failures due to SQL error(s). It appears mutations (INSERT) are getting prefixed with DECLARE temp1 NO SCROLL CURSOR FOR even though nothing to do with execute has been touched. Any thoughts from Postgres folks?

Here's a partial snippet of the database log:

2017-10-29 21:21:44.617 EDT [40271] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.617 EDT [40271] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "PersonMaybeAge"("name","age") VALUES('Michael',null)RETURNING "id"
2017-10-29 21:21:44.620 EDT [40272] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.620 EDT [40272] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('Michael',25,null) RETURNING "id"
2017-10-29 21:21:44.626 EDT [40273] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.626 EDT [40273] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES ('Michael',25,null),('Michael2',26,null) RETURNING "id"
2017-10-29 21:21:44.632 EDT [40274] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.632 EDT [40274] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES ('selectSource1',1,null),('selectSource2',2,null),('selectSource3',3,null) RETURNING "id"
2017-10-29 21:21:44.637 EDT [40275] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.637 EDT [40275] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('Michael',26,null) RETURNING "id"
2017-10-29 21:21:44.640 EDT [40276] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.640 EDT [40276] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES ('selectKeys1',1,null),('selectKeys2',2,null),('selectKeys3',3,null) RETURNING "id"
2017-10-29 21:21:44.664 EDT [40279] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.664 EDT [40279] STATEMENT:  DECLARE temp2 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('name',1,null) RETURNING "id"
2017-10-29 21:21:44.679 EDT [40281] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.679 EDT [40281] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('name',1,null) RETURNING "id"
2017-10-29 21:21:44.684 EDT [40282] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.684 EDT [40282] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('name',1,null) RETURNING "id"
2017-10-29 21:21:44.688 EDT [40283] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.688 EDT [40283] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('name',1,null) RETURNING "id"
2017-10-29 21:21:44.694 EDT [40284] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.694 EDT [40284] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('name',1,null) RETURNING "id"
2017-10-29 21:21:44.710 EDT [40286] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.710 EDT [40286] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('pet owner',30,null) RETURNING "id"
2017-10-29 21:21:44.717 EDT [40287] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.717 EDT [40287] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('pet owner',30,null) RETURNING "id"
2017-10-29 21:21:44.721 EDT [40288] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.721 EDT [40288] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('pet owner',30,null) RETURNING "id"
2017-10-29 21:21:44.727 EDT [40289] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.727 EDT [40289] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('pet owner',30,null) RETURNING "id"
2017-10-29 21:21:44.732 EDT [40290] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.732 EDT [40290] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('D',0,null) RETURNING "id"
2017-10-29 21:21:44.737 EDT [40291] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.737 EDT [40291] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('D',0,null) RETURNING "id"
2017-10-29 21:21:44.743 EDT [40292] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.743 EDT [40292] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('D',0,null) RETURNING "id"
2017-10-29 21:21:44.767 EDT [40296] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.767 EDT [40296] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('Mathias',23,null) RETURNING "id"
2017-10-29 21:21:44.775 EDT [40298] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.775 EDT [40298] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('Zacarias',93,null) RETURNING "id"
2017-10-29 21:21:44.782 EDT [40299] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.782 EDT [40299] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('Mathias',23,null) RETURNING "id"
2017-10-29 21:21:44.787 EDT [40300] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.787 EDT [40300] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person1"("name","age") VALUES('foo',0) RETURNING "id"
2017-10-29 21:21:44.794 EDT [40302] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.794 EDT [40302] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "no_prefix1"("some_field_name") VALUES(1) RETURNING "id"
2017-10-29 21:21:44.800 EDT [40304] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.800 EDT [40304] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "Person"("name","age","color") VALUES('Alice',30,null) RETURNING "id"
2017-10-29 21:21:44.805 EDT [40305] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.805 EDT [40305] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "UserPT"("ident","password") VALUES ('a','b'),('c','d'),('e',null),('g','h') RETURNING "id"
2017-10-29 21:21:44.813 EDT [40306] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.813 EDT [40306] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "UserPT"("ident","password") VALUES ('a','b'),('c','d'),('e',null),('g','h') RETURNING "id"
2017-10-29 21:21:44.857 EDT [40308] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.857 EDT [40308] STATEMENT:  DECLARE temp7 NO SCROLL CURSOR FOR INSERT INTO "empty_entity" DEFAULT VALUES RETURNING "id"
2017-10-29 21:21:44.934 EDT [40320] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.934 EDT [40320] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "test_child"("name","name2","age","extra4") VALUES('a1','b1',11,'c1') RETURNING "id"
2017-10-29 21:21:44.983 EDT [40326] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.983 EDT [40326] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "test_child"("name","name2","age","extra4") VALUES('a1','b1',11,'c1') RETURNING "id"
2017-10-29 21:21:44.987 EDT [40327] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.987 EDT [40327] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "citizen"("name","age") VALUES('mk',11) RETURNING "id"
2017-10-29 21:21:44.993 EDT [40328] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:44.993 EDT [40328] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "test_parent"("name","name2","age","extra44") VALUES ('a1','b1',11,'p1'),('a2','b2',22,'p2') RETURNING "name", "name2", "age"
2017-10-29 21:21:45.028 EDT [40333] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:45.028 EDT [40333] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "bar"("quux") VALUES('name') RETURNING "id"
2017-10-29 21:21:45.040 EDT [40335] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:45.040 EDT [40335] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "blog_post"("article") VALUES('article') RETURNING "id"
2017-10-29 21:21:45.048 EDT [40336] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:21:45.048 EDT [40336] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "tweet_url"("tweet_id","tweet_url","final_url") VALUES(1,'http://google.com','http://example.com') RETURNING "id"
2017-10-29 21:31:07.511 EDT [40381] ERROR:  relation "blog_post" does not exist at character 13
2017-10-29 21:31:07.511 EDT [40381] STATEMENT:  INSERT INTO "blog_post"("article") VALUES('article') RETURNING "id";
2017-10-29 21:31:25.844 EDT [40383] ERROR:  syntax error at or near "INSERT" at character 36
2017-10-29 21:31:25.844 EDT [40383] STATEMENT:  DECLARE temp1 NO SCROLL CURSOR FOR INSERT INTO "blog_post"("article") VALUES('article') RETURNING "id";

bitemyapp · 2018-03-20T15:14:37Z

I don't think this is something that is sensible/safe to do unconditionally. Possibly it should be associated with a function that has a type constraining itself to database read actions using the read/write roles.

I've been doing related'ish work in Esqueleto: https://github.com/bitemyapp/esqueleto/tree/bitemyapp/validate-read-write-roles

I'm also interested in this because I'd like to see streaming sorted out for the next time I need it.

parsonsmatt

Ran into some test failures due to SQL error(s). It appears mutations (INSERT) are getting prefixed with DECLARE temp1 NO SCROLL CURSOR FOR even though nothing to do with execute has been touched. Any thoughts from Postgres folks?

Yeah - that is an expected behavior of fold. I think that swapping for foldWithOption might fix this issue.

I think I agree with @bitemyapp that this isn't the right place to implement this functionality. It isn't unconditionally faster (the docs on fold say that smaller queries/results will be faster with query), and it is (should be?) limited to ReadOnly queries. I'd be 100% happy to include an addition to the API that gives this functionality, and I would like to see selectSource be made more extensible so that it can take advantage of this.

persistent-postgresql/Database/Persist/Postgresql.hs

Since postgres-simple's `fold` + `FromField` instance can handle nearly all the work, libpq dependency has now been fully removed.

…ude.

`fold` demands we strictly consume each row to avoid space leaks: https://www.stackage.org/haddock/lts-14.19/postgresql-simple-0.6.2/Database-PostgreSQL-Simple.html#v:fold

naushadh · 2019-12-28T20:49:56Z

@parsonsmatt tried to use foldWithOptions but the issue persists

options = PG.FoldOptions PG.Automatic $ PG.TransactionMode PG.DefaultIsolationLevel PG.ReadOnly
openS = PG. foldWithOptions options conn query (map P vals) CL.sourceNull processRow

As noted before, looks like selectSource needs its own withStmt' implementation that uses fold In a READ only connection to support cursor based streaming.

naushadh · 2020-08-24T14:50:10Z

Closing stale PR

naushadh mentioned this pull request Jan 17, 2018

selectSource does not stream results #657

Open

parsonsmatt requested changes Dec 28, 2019

View reviewed changes

persistent-postgresql/Database/Persist/Postgresql.hs Outdated Show resolved Hide resolved

persistent-postgresql/Database/Persist/Postgresql.hs Show resolved Hide resolved

naushadh added 3 commits December 28, 2019 14:26

[yesodweb#657] Attempt streaming results in withStmt.

5c02e3b

Since postgres-simple's `fold` + `FromField` instance can handle nearly all the work, libpq dependency has now been fully removed.

[yesodweb#657] Removed <$> since its not available in old base Prel…

36c1859

…ude.

Strictly consume each row.

7e39b02

`fold` demands we strictly consume each row to avoid space leaks: https://www.stackage.org/haddock/lts-14.19/postgresql-simple-0.6.2/Database-PostgreSQL-Simple.html#v:fold

naushadh force-pushed the postgres-selectSource branch from a298d81 to 7e39b02 Compare December 28, 2019 20:45

naushadh closed this Aug 24, 2020

naushadh deleted the postgres-selectSource branch August 24, 2020 14:56

ivb-supercede mentioned this pull request Jul 15, 2021

Add API for mem-constant DB streams, Postgres implementation #1298

Closed

7 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Make PostgreSQL `selectSource` stream results. #726

Make PostgreSQL `selectSource` stream results. #726

Uh oh!

naushadh commented Oct 30, 2017 •

edited

Loading

Uh oh!

naushadh commented Oct 30, 2017

Uh oh!

bitemyapp commented Mar 20, 2018

Uh oh!

parsonsmatt left a comment

Uh oh!

Uh oh!

Uh oh!

naushadh commented Dec 28, 2019 •

edited

Loading

Uh oh!

naushadh commented Aug 24, 2020

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Make PostgreSQL selectSource stream results. #726

Make PostgreSQL selectSource stream results. #726

Uh oh!

Conversation

naushadh commented Oct 30, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

naushadh commented Oct 30, 2017

Uh oh!

bitemyapp commented Mar 20, 2018

Uh oh!

parsonsmatt left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

naushadh commented Dec 28, 2019 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

naushadh commented Aug 24, 2020

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Make PostgreSQL `selectSource` stream results. #726

Make PostgreSQL `selectSource` stream results. #726

naushadh commented Oct 30, 2017 •

edited

Loading

naushadh commented Dec 28, 2019 •

edited

Loading