Re: [BUGS] BUG #6698: sub-query with join producing out of memory in where clause
On 19.06.2012 04:01, armando.mirag...@stud-inf.unibz.it wrote:
The following bug has been logged on the website:
Bug reference: 6698
Logged by: Armando Miraglia
Email address: armando.mirag...@stud-inf.unibz.it
PostgreSQL version: 9.1.2
Operating system: Arch Linux/Ubuntu
Description:
Hi everybody!
Fact: while I was trying to produce a c-function I got an OOM which
RhodiumToad helped me to debug. The OOM is reproducible with also standard
query.
Environment: I tested the POC using 9.1.2 but also 9.2devel compiled
"by-hand"
Reproducibility:
- limit the memory usage
ulimit -S -v 50
- start postgresql
postgres -D ../data.ascii/
- run the following query from psql
SELECT *
FROM generate_series(1,100) i
WHERE 100<= (SELECT COUNT(*)
FROM unnest(array(select j from
generate_series(i-100,i+100) j)) u1
JOIN
unnest(array(select j from
generate_series(i-100,i+100) j)) u2
ON (u1.u1=u2.u2));
Error:
- psql side:
ERROR: out of memory
DETAIL: Failed on request of size 828.
- server side:
...
PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
PortalHeapMemory: 1024 total in 1 blocks; 824 free (0 chunks); 200 used
ExecutorState: 458358928 total in 67 blocks; 794136 free (15965
chunks); 457564792 used
accumArrayResult: 8192 total in 1 blocks; 5744 free (4 chunks); 2448
used
HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
HashBatchContext: 32768 total in 2 blocks; 8416 free (1 chunks);
24352 used
...
This test case can be further reduced into:
explain analyze
SELECT * FROM generate_series(1,10) i
WHERE (SELECT array(select repeat('a', 1) || i) u1) is not null;
We're leaking the array constructed on each row, in ExecSetParamPlan().
At line 1000 in nodeSubplan.c, we create a new array and store it as the
value of the PARAM_EXEC parameter. But it's never free'd. The old value
of the parameter is simply overwritten.
I'm not sure what the correct fix is. I suppose we could pfree() the old
value before overwriting it, but I'm not sure if that's safe, or if
there might still be references to the old value somewhere in the executor.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5823: launchd execution
Ronald, Did you ever find a solution to this bug? I'm fairly new to PostgreSQL and I've been fighting with this for weeks. I think I'm going to be forced to use MySQL instead even though I'd rather not. 6/18/12 7:42:35.188 PM com.apple.launchd: (com.edb.launchd.postgresql-9.1[3736]) Exited with code: 1 6/18/12 7:42:24.985 PM com.apple.launchd: (com.edb.launchd.postgresql-9.1) Throttling respawn: Will start in 10 seconds Best Regards, Leif Arthur Halvorsen Bonneau Lab New York University lah...@nyu.edu
[BUGS] BUG #6699: pg_restore with -j -- doesn't restore view that groups by primary key
The following bug has been logged on the website: Bug reference: 6699 Logged by: Joe Van Dyk Email address: j...@tanga.com PostgreSQL version: 9.1.4 Operating system: OSX Description: $ pg_restore -O -j 4 ~/tanga.dump -d tanga_dev_full_backup pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 417; 1259 66296 VIEW site_channels monkey pg_restore: [archiver (db)] could not execute query: ERROR: column "channels.start_at" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: SELECT channels.id, channels.start_at, channels.end_at, ... ^ Command was: CREATE VIEW site_channels AS SELECT channels.id, channels.start_at, channels.end_at, channels.title, channels.descriptio... site_channels view definition: View definition: SELECT channels.id, channels.start_at, channels.end_at, channels.title FROM channels LEFT JOIN channels_products cp ON cp.channel_id = channels.id LEFT JOIN buyable_products bp ON bp.id = cp.product_id GROUP BY channels.id; channels.id is a primary key. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6699: pg_restore with -j -- doesn't restore view that groups by primary key
On Tue, Jun 19, 2012 at 07:49:20PM +, j...@tanga.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6699
> Logged by: Joe Van Dyk
> Email address: j...@tanga.com
> PostgreSQL version: 9.1.4
> Operating system: OSX
> Description:
>
> $ pg_restore -O -j 4 ~/tanga.dump -d tanga_dev_full_backup
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 417; 1259 66296 VIEW
> site_channels monkey
> pg_restore: [archiver (db)] could not execute query: ERROR: column
> "channels.start_at" must appear in the GROUP BY clause or be used in an
> aggregate function
> LINE 2: SELECT channels.id, channels.start_at, channels.end_at, ...
> ^
> Command was: CREATE VIEW site_channels AS
> SELECT channels.id, channels.start_at, channels.end_at, channels.title,
> channels.descriptio...
>
>
>
> site_channels view definition:
>
> View definition:
> SELECT channels.id, channels.start_at, channels.end_at, channels.title
>FROM channels
>LEFT JOIN channels_products cp ON cp.channel_id = channels.id
>LEFT JOIN buyable_products bp ON bp.id = cp.product_id
> GROUP BY channels.id;
>
> channels.id is a primary key.
Attached is a test case to reproduce the problem, courtesy of the
original reporter.
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
-Ryan
$ cat t.sql
create table foo (id serial primary key, name text);
insert into foo (name) values ('joe');
create view v as (select id, name from foo group by id);
$ createdb test
$ psql -f t.sql test
psql:t.sql:1: NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq"
for serial column "foo.id"
psql:t.sql:1: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
INSERT 0 1
CREATE VIEW
$ pg_dump -Fc test > t.dump
$ dropdb test
$ createdb test
$ pg_restore -j4 -d test t.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 163; 1259 3870794 VIEW v joe
pg_restore: [archiver (db)] could not execute query: ERROR: column "foo.name"
must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: SELECT foo.id, foo.name FROM foo GROUP BY foo.id;
^
Command was: CREATE VIEW v AS
SELECT foo.id, foo.name FROM foo GROUP BY foo.id;
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"public.v" does not exist
Command was: ALTER TABLE public.v OWNER TO joe;
WARNING: errors ignored on restore: 2
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6699: pg_restore with -j -- doesn't restore view that groups by primary key
Excerpts from Ryan Kelly's message of mar jun 19 16:20:58 -0400 2012: > On Tue, Jun 19, 2012 at 07:49:20PM +, j...@tanga.com wrote: > > View definition: > > SELECT channels.id, channels.start_at, channels.end_at, channels.title > >FROM channels > >LEFT JOIN channels_products cp ON cp.channel_id = channels.id > >LEFT JOIN buyable_products bp ON bp.id = cp.product_id > > GROUP BY channels.id; > > > > channels.id is a primary key. > > Attached is a test case to reproduce the problem, courtesy of the > original reporter. The reason this doesn't work is that the primary key is not defined until later in the restore process. I think the fix is to make the view dependant on the primary key in the dump file. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6699: pg_restore with -j -- doesn't restore view that groups by primary key
Alvaro Herrera writes: > Excerpts from Ryan Kelly's message of mar jun 19 16:20:58 -0400 2012: >> On Tue, Jun 19, 2012 at 07:49:20PM +, j...@tanga.com wrote: >>> SELECT channels.id, channels.start_at, channels.end_at, channels.title >>> FROM channels >>> LEFT JOIN channels_products cp ON cp.channel_id = channels.id >>> LEFT JOIN buyable_products bp ON bp.id = cp.product_id >>> GROUP BY channels.id; > The reason this doesn't work is that the primary key is not defined > until later in the restore process. > I think the fix is to make the view dependant on the primary key in the > dump file. Hmm ... check_functional_grouping does add the PK's OID to the query's constraintDeps list. Apparently we're losing that dependency knowledge somewhere between the parser and pg_dump? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6699: pg_restore with -j -- doesn't restore view that groups by primary key
I wrote: > Hmm ... check_functional_grouping does add the PK's OID to the query's > constraintDeps list. Apparently we're losing that dependency knowledge > somewhere between the parser and pg_dump? I looked into this a bit. The dependency does exist in pg_depend, but it is shown as a dependency from the view's _RETURN rule to the constraint, not as a dependency of the view itself. Using the simplified example create table t1 (f1 int primary key, f2 text); create view v1 as select * from t1 group by f1; what you get from "pg_dump -Fc | pg_restore -l -v" is ; Selected TOC Entries: ; 1923; 0 0 ENCODING - ENCODING 1924; 0 0 STDSTRINGS - STDSTRINGS 1925; 1262 41967 DATABASE - refbug postgres 5; 2615 2200 SCHEMA - public postgres 1926; 0 0 COMMENT - SCHEMA public postgres ; depends on: 5 1927; 0 0 ACL - public postgres ; depends on: 5 170; 3079 11727 EXTENSION - plpgsql 1928; 0 0 COMMENT - EXTENSION plpgsql ; depends on: 170 168; 1259 41968 TABLE public t1 postgres ; depends on: 5 1921; 2606 41975 CONSTRAINT public t1_pkey postgres ; depends on: 168 168 169; 1259 41976 VIEW public v1 postgres ; depends on: 1919 5 1922; 0 41968 TABLE DATA public t1 postgres ; depends on: 168 So the view is shown as depending on "object 1919", which is nowhere to be seen, because it is the _RETURN rule which did not get dumped separately. There is therefore no way at all for pg_restore to know that the view has to be restored after the constraint. (pg_dump does know that, since it was working with full dependency info, which is why the constraint comes first in the dump order. But the info isn't exposed where pg_restore can see it.) Clearly, this is a bug in the way pg_dump emits dependency info. It never mattered before, but parallel pg_restore really needs accurate dependencies. We could possibly hack something for the special case of rules, but I don't think this would be the last time we hear about this type of issue. I'm inclined to think that the best solution would be to add generic logic to pg_dump that "looks through" any dependency references to objects that are not going to be dumped, and replaces them with the IDs of any objects they depend on that are going to be dumped. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6699: pg_restore with -j -- doesn't restore view that groups by primary key
I wrote:
> We could possibly hack something for the special case of rules, but
> I don't think this would be the last time we hear about this type of
> issue. I'm inclined to think that the best solution would be to add
> generic logic to pg_dump that "looks through" any dependency references
> to objects that are not going to be dumped, and replaces them with the
> IDs of any objects they depend on that are going to be dumped.
I wrote a trial patch that does things that way (attached) and it
appears to work and solve the problem. However, it's not committable
as-is because it breaks the build of pg_restore:
ld: Unsatisfied symbols:
findObjectByDumpId (code)
since findObjectByDumpId is in common.c which isn't linked into
pg_restore. I guess a brute force solution would be to link it,
but probably we ought to think about refactoring the code to avoid
that. I'm not coming up with any very nice ideas about exactly how
to refactor, though. Thoughts?
regards, tom lane
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index aa9c8eed5dafabd49429874d973e22abe93d5294..2db67d6fd3d5616944ff0b086aa606af11e9eed4 100644
*** a/src/bin/pg_dump/pg_backup_archiver.c
--- b/src/bin/pg_dump/pg_backup_archiver.c
*** static void _becomeUser(ArchiveHandle *A
*** 129,134
--- 129,135
static void _becomeOwner(ArchiveHandle *AH, TocEntry *te);
static void _selectOutputSchema(ArchiveHandle *AH, const char *schemaName);
static void _selectTablespace(ArchiveHandle *AH, const char *tablespace);
+ static void writeDependencies(ArchiveHandle *AH, const DumpId *dependencies, int nDeps);
static void processEncodingEntry(ArchiveHandle *AH, TocEntry *te);
static void processStdStringsEntry(ArchiveHandle *AH, TocEntry *te);
static teReqs _tocEntryRequired(TocEntry *te, teSection curSection, RestoreOptions *ropt);
*** WriteToc(ArchiveHandle *AH)
*** 2144,2150
TocEntry *te;
char workbuf[32];
int tocCount;
- int i;
/* count entries that will actually be dumped */
tocCount = 0;
--- 2145,2150
*** WriteToc(ArchiveHandle *AH)
*** 2184,2195
WriteStr(AH, te->withOids ? "true" : "false");
/* Dump list of dependencies */
! for (i = 0; i < te->nDeps; i++)
! {
! sprintf(workbuf, "%d", te->dependencies[i]);
! WriteStr(AH, workbuf);
! }
! WriteStr(AH, NULL); /* Terminate List */
if (AH->WriteExtraTocPtr)
(*AH->WriteExtraTocPtr) (AH, te);
--- 2184,2191
WriteStr(AH, te->withOids ? "true" : "false");
/* Dump list of dependencies */
! writeDependencies(AH, te->dependencies, te->nDeps);
! WriteStr(AH, NULL); /* list terminator */
if (AH->WriteExtraTocPtr)
(*AH->WriteExtraTocPtr) (AH, te);
*** ReadToc(ArchiveHandle *AH)
*** 2353,2358
--- 2349,2400
}
}
+ /*
+ * Dump an object's dependencies in a usable form, ie, referencing only
+ * objects that are included in the dump. We need to make this distinction
+ * since, for example, a view will depend on its _RETURN rule while the
+ * _RETURN rule will depend on other objects --- but the rule will not appear
+ * as a separate object in the dump. We have to look through the rule to get
+ * a useful representation of the view's dependencies.
+ *
+ * We rely here on the assumption that sortDumpableObjects already broke any
+ * dependency loops, else we might recurse infinitely.
+ */
+ static void
+ writeDependencies(ArchiveHandle *AH, const DumpId *dependencies, int nDeps)
+ {
+ char workbuf[32];
+ int i;
+
+ for (i = 0; i < nDeps; i++)
+ {
+ DumpId depid = dependencies[i];
+ TocEntry *depte = getTocEntryByDumpId(AH, depid);
+
+ if (depte &&
+ (depte->reqs & (REQ_SCHEMA | REQ_DATA | REQ_SPECIAL)) != 0)
+ {
+ /* Object will be dumped, so just reference it as a dependency */
+ /* Dependency IDs are printed as strings for historical reasons */
+ sprintf(workbuf, "%d", depid);
+ WriteStr(AH, workbuf);
+ }
+ else
+ {
+ /*
+ * Object will not be dumped, so recursively consider its deps.
+ * Here we must resort to the underlying DumpableObject data
+ * structures, since often there will be no TocEntry for such
+ * an object.
+ */
+ DumpableObject *dobj = findObjectByDumpId(depid);
+
+ if (dobj)
+ writeDependencies(AH, dobj->dependencies, dobj->nDeps);
+ }
+ }
+ }
+
static void
processEncodingEntry(ArchiveHandle *AH, TocEntry *te)
{
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6699: pg_restore with -j -- doesn't restore view that groups by primary key
I wrote: > 168; 1259 41968 TABLE public t1 postgres > ; depends on: 5 > 1921; 2606 41975 CONSTRAINT public t1_pkey postgres > ; depends on: 168 168 > 169; 1259 41976 VIEW public v1 postgres > ; depends on: 1919 5 > 1922; 0 41968 TABLE DATA public t1 postgres > ; depends on: 168 BTW, there's another pretty unpleasant thing going on here, which is that the t1_pkey constraint is getting hoisted up to before t1's table data because it is a dependency of v1. That means the index will be created before the data is loaded, which is not what we want. Parallel pg_restore actually has a hack that should work around that, namely repoint_table_dependencies(). That doesn't help for plain serial restores though. I'm thinking we really ought to bite the bullet and do something comparable to repoint_table_dependencies() at an appropriate point in pg_dump, so that the dependencies are sane to begin with. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5823: launchd execution
On 06/19/2012 07:43 AM, Leif Halvorsen wrote: Ronald, Did you ever find a solution to this bug? I'm fairly new to PostgreSQL and I've been fighting with this for weeks. I think I'm going to be forced to use MySQL instead even though I'd rather not. 6/18/12 7:42:35.188 PM com.apple.launchd: (com.edb.launchd.postgresql-9.1[3736]) Exited with code: 1 6/18/12 7:42:24.985 PM com.apple.launchd: (com.edb.launchd.postgresql-9.1) Throttling respawn: Will start in 10 seconds What's in the system logs (Console.app) ? The PostgreSQL logs? -- Craig Ringer
Re: [BUGS] BUG #6698: sub-query with join producing out of memory in where clause
> I'm not sure what the correct fix is. I suppose we could pfree() the old > value before overwriting it, but I'm not sure if that's safe, or if > there might still be references to the old value somewhere in the executor. It will resolve the current problem but I am also not sure whether it can create any other problem because in this function most of the work is done in per-query memory context. One thing if we can clarify that why per-tuple memory context is not sufficient for this value than it can be easy to conclude on solution for this problem. Another thing I have noticed is that in function ExecScanSubPlan, the similar work is not done in Per-query memory context, I am not sure if it is of any relevance for the problem you mentioned. With Regards, Amit Kapila. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
