Re: [BUGS] BUG #8257: Multi-Core Restore fails when containing index comments
Hi Lloyd,
On 2013-06-26 23:43:00 +, lal...@fhcrc.org wrote:
> I have found the restore will fail when using pg_restore's -j option, with
> more than one core, on a dump that contains a COMMENT INDEX.
> Run this next section to add the table, index, and index comment to the
> test_db database.
> CREATE TABLE public.tbl_test (
> pkey TEXT NOT NULL,
> CONSTRAINT tbl_test_pkey PRIMARY KEY(pkey)
> );
> COMMENT ON INDEX public.tbl_test_pkey
> IS 'Index Comment';
> Once this test database is created, create a backup of the database.
> pg_dump -Fc test_db > test_db.dump
The problem is that pg_dump makes the comment depend on the index
instead of the constraint:
; Selected TOC Entries:
...
170; 1259 69261 TABLE public tbl_test andres
;depends on: 6
1941; 0 69261 TABLE DATA public tbl_test andres
; depends on: 170
1833; 2606 69268 CONSTRAINT public tbl_test_pkey andres
; depends on: 170 170
1950; 0 0 COMMENT public INDEX tbl_test_pkey andres
; depends on: 1832
There is no object 1832 in the dump since that was ommitted in favor of
the constraint 1833 which internally creates the index. So what we need
to do is to make the comment depend on the constraint instead.
With the attached patch we get:
170; 1259 69261 TABLE public tbl_test andres
;depends on: 6
1941; 0 69261 TABLE DATA public tbl_test andres
; depends on: 170
1833; 2606 69268 CONSTRAINT public tbl_test_pkey andres
; depends on: 170 170
1950; 0 0 COMMENT public INDEX tbl_test_pkey andres
; depends on: 1833
unsurprisingly after that restore completes.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 2ce0cd8..107cabb 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -13493,6 +13493,7 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
PQExpBuffer q;
PQExpBuffer delq;
PQExpBuffer labelq;
+ bool is_constraint = indxinfo->indexconstraint != 0;
if (dataOnly)
return;
@@ -13509,7 +13510,7 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
* do dump any comment for it. (This is safe because dependency ordering
* will have ensured the constraint is emitted first.)
*/
- if (indxinfo->indexconstraint == 0)
+ if (!is_constraint)
{
if (binary_upgrade)
binary_upgrade_set_pg_class_oids(fout, q,
@@ -13547,11 +13548,15 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo)
NULL, NULL);
}
- /* Dump Index Comments */
+ /*
+ * Dump Index Comments - depend on the constraint instead of the index if
+ * present to ensure sensible ordering.
+ */
dumpComment(fout, labelq->data,
tbinfo->dobj.namespace->dobj.name,
tbinfo->rolname,
-indxinfo->dobj.catId, 0, indxinfo->dobj.dumpId);
+indxinfo->dobj.catId, 0,
+ is_constraint ? indxinfo->indexconstraint : indxinfo->dobj.dumpId);
destroyPQExpBuffer(q);
destroyPQExpBuffer(delq);
--
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 #8255: encoding latin1
On Wed, Jun 26, 2013 at 9:21 PM, wrote: > The following bug has been logged on the website: > > Bug reference: 8255 > Logged by: gabriel > Email address: gabriel...@santamonicace.com.br > PostgreSQL version: 8.4.4 You are missing many minor releases. The latest version of 8.4 is 8.4.17. It will also fall in EOL last year, so think about an upgrade! > Operating system: windows xp > Description: > > Good afternoon, I live in Brazil and I need to create a database with > encoding LATIN1. how can I make this database. I use windows xp and > postgreSQL. please send a tutorial on how to makeThank you. initdb has an option called --encoding allowing to override the encoding of the template database when initializing server. You can also specify an encoding with CREATE DATABASE, but use template0 as template database in this case. postgres=# create database foo encoding 'LATIN1' TEMPLATE template0; CREATE DATABASE For reference: http://www.postgresql.org/docs/8.4/static/app-initdb.html http://www.postgresql.org/docs/9.3/static/sql-createdatabase.html -- Michael -- 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 #8258: I am not able to search composite types in search object
The following bug has been logged on the website: Bug reference: 8258 Logged by: Varun Email address: varunkark...@gmail.com PostgreSQL version: 9.2.4 Operating system: Windows 7 Description: hello, object Regards -- 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 #8259: Not able to search composite type in search object
The following bug has been logged on the website: Bug reference: 8259 Logged by: Varun Email address: varunkark...@gmail.com PostgreSQL version: 9.2.4 Operating system: Win 7 Description: Hello, NOt able to search composite type in search object Ragrds -- 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 #8257: Multi-Core Restore fails when containing index comments
Andres Freund writes: > The problem is that pg_dump makes the comment depend on the index > instead of the constraint: Yeah, I figured that out yesterday, but hadn't gotten to writing a patch yet. > ... So what we need > to do is to make the comment depend on the constraint instead. Your proposed patch will only fix the problem for dumps created after it ships. In the past, we've tried to deal with this type of issue by having pg_restore fix up the dependencies when reading a dump, so that it would still work on existing dumps. I'm afraid there may be no way to do that in this case --- it doesn't look like there's enough info in the dump to tell where the dependency link should have led. But we should think about it a little before taking the easy way out. 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 #8257: Multi-Core Restore fails when containing index comments
On 2013-06-27 10:29:14 -0400, Tom Lane wrote: > > ... So what we need > > to do is to make the comment depend on the constraint instead. > Your proposed patch will only fix the problem for dumps created after > it ships. In the past, we've tried to deal with this type of issue by > having pg_restore fix up the dependencies when reading a dump, so that > it would still work on existing dumps. Yes :(. On the other hand, it's probably not too common to create comments on indexes that haven't been created explicitly. > I'm afraid there may be no way to do that in this case --- it doesn't > look like there's enough info in the dump to tell where the dependency > link should have led. But we should think about it a little before > taking the easy way out. The only thing I could think of - but which I thought to be too kludgey - was to simply delay the creation of all comments and restore them together with ACLs. I don't think we can have dependencies towards comments. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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 #8260: problem with sequence and tablename
The following bug has been logged on the website: Bug reference: 8260 Logged by: Daniel Degasperi Email address: daniel.degasp...@r3-gis.com PostgreSQL version: 9.1.7 Operating system: Centos 6.4 Description: I've created a table: CREATE TABLE t23456789012345678901234567890123456789012345678901 ( t1234_id serial, PRIMARY KEY (t1234_id) ) WITH ( OIDS=FALSE ); This generated the sequence: CREATE SEQUENCE t2345678901234567890123456789012345678901234567890_t1234_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; The table-part in the sequence name was truncated. Best regards, Daniel -- 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 #8260: problem with sequence and tablename
Object names are limited to a length of NAMEDATALEN-1, which defaults to 63, and are documented to be truncated. http://www.postgresql.org/docs/9.2/static/sql-syntax-lexical.html The behaviour you are observing appears to conform to the documentation, and seems reasonable, so I wouldn't think this was a bug.
Re: [BUGS] BUG #8260: problem with sequence and tablename
daniel.degasp...@r3-gis.com writes: > CREATE TABLE t23456789012345678901234567890123456789012345678901 > ( > t1234_id serial, > PRIMARY KEY (t1234_id) > ) > WITH ( > OIDS=FALSE > ); > This generated the sequence: > CREATE SEQUENCE > t2345678901234567890123456789012345678901234567890_t1234_id_seq That's operating as designed. > The table-part in the sequence name was truncated. Would you rather it failed entirely? You're up against the limit on name length (63 bytes in a standard Postgres build). 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 #8257: Multi-Core Restore fails when containing index comments
Andres Freund writes: > On 2013-06-27 10:29:14 -0400, Tom Lane wrote: >> Your proposed patch will only fix the problem for dumps created after >> it ships. In the past, we've tried to deal with this type of issue by >> having pg_restore fix up the dependencies when reading a dump, so that >> it would still work on existing dumps. > Yes :(. On the other hand, it's probably not too common to create > comments on indexes that haven't been created explicitly. Perhaps. The lack of previous complaints does suggest this situation isn't so common. >> I'm afraid there may be no way to do that in this case --- it doesn't >> look like there's enough info in the dump to tell where the dependency >> link should have led. But we should think about it a little before >> taking the easy way out. > The only thing I could think of - but which I thought to be too kludgey > - was to simply delay the creation of all comments and restore them > together with ACLs. I don't like that either, though we may be forced into it if we find more bugs in comment dependencies. Anyway, fixing pg_dump's logic is not wrong; I was just hoping we could also think of a workaround on the pg_restore side. 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 #8257: Multi-Core Restore fails when containing index comments
Andres Freund writes: > There is no object 1832 in the dump since that was ommitted in favor of > the constraint 1833 which internally creates the index. So what we need > to do is to make the comment depend on the constraint instead. > With the attached patch we get: [ the right thing ] Applied with minor cosmetic changes. 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
[BUGS] BUG #8266: Problem with \d and ::regclass when tables have utf8 chars in their name
The following bug has been logged on the website: Bug reference: 8266 Logged by: Denis de Bernardy Email address: ddeberna...@yahoo.com PostgreSQL version: 9.2.4 Operating system: OSX Lion Description: As identified in this StackOverflow thread: http://stackoverflow.com/questions/17353469/postgresql-and-unicode-table-names-why-can-i-not-select-the-table-name-from-the/ --- # create table pinkƒpink1 (id serial); NOTICE: CREATE TABLE will create implicit sequence "pink?pink1_id_seq" for serial column "pink?pink1.id" CREATE TABLE # select 'pinkƒpink1'::name; name pinkƒpink1 (1 row) # select 'pinkƒpink1'::regclass; regclass - "pinkpink1" (1 row) # select relname from pg_class where oid = 'pinkƒpink1'::regclass; relname --- pinkpink1 # select relname from pg_class where relname = 'pinkƒpink1'::name; relname - (0 rows) # select relname from pg_class where relname = 'pinkpink1'; relname - (0 rows) --- It doesn't seem to affect Debian. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
