Re: [BUGS] BUG #6706: pg_upgrade fails when plpgsql dropped/re-created
On Sat, Jun 30, 2012 at 01:00:07AM -0400, Bruce Momjian wrote:
> On Fri, Jun 29, 2012 at 11:35:15PM -0400, Tom Lane wrote:
> > Bruce Momjian writes:
> > > I can't think of how to fix this. Perhaps we need to query the
> > > pg_extension table as of the SELECT function all.
> >
> > I think you're misjudging the core of the issue. The same thing
> > would happen if somebody dropped and recreated the public schema.
> > Or anything else that we create at initdb time but allow to be
> > dropped.
>
> I just tested dropping and recreating the 'public' schema and pg_upgrade
> worked fine.
>
> I think the fix we need for extensions is to change:
>
> SELECT binary_upgrade.create_empty_extension('plpgsql',
> 'pg_catalog', false, '1.0', NULL, NULL,
> ARRAY[]::pg_catalog.text[]);
>
> to
>
> SELECT binary_upgrade.create_empty_extension('plpgsql',
> 'pg_catalog', false, '1.0', NULL, NULL, ARRAY[]::pg_catalog.text[])
> WHERE (SELECT COUNT(*) FROM pg_extension WHERE extname = 'plpgsql') = 0;
>
> This basically conditionally calls
> binary_upgrade.create_empty_extension() based on whether the extension
> already exists in the new cluster.
FYI, I forgot to mention that there is a unique index on extname, so
testing just for the name should work fine.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #6706: pg_upgrade fails when plpgsql dropped/re-created
On Fri, Jun 29, 2012 at 11:35:15PM -0400, Tom Lane wrote:
> Bruce Momjian writes:
> > I can't think of how to fix this. Perhaps we need to query the
> > pg_extension table as of the SELECT function all.
>
> I think you're misjudging the core of the issue. The same thing
> would happen if somebody dropped and recreated the public schema.
> Or anything else that we create at initdb time but allow to be
> dropped.
I just tested dropping and recreating the 'public' schema and pg_upgrade
worked fine.
I think the fix we need for extensions is to change:
SELECT binary_upgrade.create_empty_extension('plpgsql',
'pg_catalog', false, '1.0', NULL, NULL,
ARRAY[]::pg_catalog.text[]);
to
SELECT binary_upgrade.create_empty_extension('plpgsql',
'pg_catalog', false, '1.0', NULL, NULL, ARRAY[]::pg_catalog.text[])
WHERE (SELECT COUNT(*) FROM pg_extension WHERE extname = 'plpgsql') = 0;
This basically conditionally calls
binary_upgrade.create_empty_extension() based on whether the extension
already exists in the new cluster.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #6706: pg_upgrade fails when plpgsql dropped/re-created
Bruce Momjian writes: > I can't think of how to fix this. Perhaps we need to query the > pg_extension table as of the SELECT function all. I think you're misjudging the core of the issue. The same thing would happen if somebody dropped and recreated the public schema. Or anything else that we create at initdb time but allow to be dropped. 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 #6706: pg_upgrade fails when plpgsql dropped/re-created
On Mon, Jun 25, 2012 at 10:57:56PM +, m.sakre...@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6706
> Logged by: Maciek Sakrejda
> Email address: m.sakre...@gmail.com
> PostgreSQL version: Unsupported/Unknown
> Operating system: Ubuntu 12.04 LTS (3.2.0-25-generic x86_64)
> Description:
>
> Using the 9.2beta2 of pg_upgrade and for the upgrade-to cluster.
>
> Ran into an issue upgrading a 9.1 cluster via pg_upgrade. I can reproduce it
> consistently:
>
> 1. Create a 9.1 cluster
> 2. Run "drop extension plpgsql" as superuser
> 3. Run "create extension plpgsql" as non-superuser
> 4. Perform normal upgrade via pg_upgrade
>
> The last step fails and I get the following error in
> pg_upgrade_restore.log:
>
> SELECT binary_upgrade.create_empty_extension('plpgsql', 'pg_catalog', false,
> '1.0', NULL, NULL, ARRAY[]::pg_catalog.text[]);
> psql:pg_upgrade_dump_db.sql:40: ERROR: duplicate key value violates unique
> constraint "pg_extension_name_index"
> DETAIL: Key (extname)=(plpgsql) already exists.
I can easily recreate this failure, even doing the drop/create as
super-user. Fortunately the cause is clearly outlined in the C comments
of pg_dump.c:
if (!binary_upgrade)
{
/*
* In a regular dump, we use IF NOT EXISTS so that there isn't a
* problem if the extension already exists in the target database;
* this is essential for installed-by-default extensions such as
* plpgsql.
*
* In binary-upgrade mode, that doesn't work well, so instead we skip
* built-in extensions based on their OIDs; see
* selectDumpableExtension.
*/
appendPQExpBuffer(q, "CREATE EXTENSION IF NOT EXISTS %s WITH SCHEMA
%s;\n",
qextname, fmtId(extinfo->namespace));
}
else
{
int i;
int n;
appendPQExpBuffer(q, "-- For binary upgrade, create an empty extension
and insert objects into it\n");
appendPQExpBuffer(q,
"SELECT binary_upgrade.create_empty_extension(");
For non-binary-upgrade dumps, IF NOT EXISTS easily allows drop/create of
plpgsql to work. In binary-upgrade mode, selectDumpableExtension()
dumps all extensions that have an oid greater than FirstNormalObjectId.
This is the only use of FirstNormalObjectId in the pg_dump code, and
obviously something that needs attention. Other objects are skipped if
they exist in pg_catalog, but extensions are always in pg_catalog, so
that filter will not work.
I can't think of how to fix this. Perhaps we need to query the
pg_extension table as of the SELECT function all. A workaround is to
renumber the oid of the plpgsql pg_extension row to be less than
FirstNormalObjectId, but that is hardly user-friendly.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] Feature Request (and/or possible bug) re Default Tablespaces
r d writes:
> My Database C1 definition:
> CREATE DATABASE "C1" ...
>TABLESPACE = "C1"
> After these two statements, tablespace C1 should be the default tablespace
> for storing all objects in database C1, meaning that when I do not specify
> a tablespace, objects are stored there. Right?
Right.
> I now proceed to do:
> CREATE TABLE "TEMP1"
> (
> "ID" bigint NOT NULL,
> CONSTRAINT "PK_TEMP1" PRIMARY KEY ("ID" )
> );
> That makes a table "TEMP1" in tablespace C1. Ok so far.
> *But it creates the index of the PK in tablespace "pg_default" !!!*
Um ... not for me. AFAICS, nothing is created under $PGDATA/base
when I do this. And both the table and the index show up in pg_class
with reltablespace = 0:
d1=# select relname,reltablespace from pg_class where relname like '%TEMP%';
relname | reltablespace
--+---
TEMP1| 0
PK_TEMP1 | 0
(2 rows)
which is the correct way of indicating they belong to the database's
default tablespace. Are you perhaps misinterpreting the zero as meaning
they'll be in pg_default?
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] Feature Request (and/or possible bug) re Default Tablespaces
Hi,
I'll first explain how it is now and then I'll explain how I think it
should behave :-)
I have Postgresql 9.1.4 64bit Linux
- The default tablespace at install is "pg_default" which is (here)
somewhere in /var/lib/...
- The parameter "default_tablespace" in *postgresql.conf* is commented out
(it's original state)
My Tablespace C1 definition:
CREATE TABLESPACE "C1"
OWNER postgres
LOCATION '/some/path';
My Database C1 definition:
CREATE DATABASE "C1"
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = "C1"
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8'
CONNECTION LIMIT = -1;
After these two statements, tablespace C1 should be the default tablespace
for storing all objects in database C1, meaning that when I do not specify
a tablespace, objects are stored there. Right?
I now proceed to do:
CREATE TABLE "TEMP1"
(
"ID" bigint NOT NULL,
CONSTRAINT "PK_TEMP1" PRIMARY KEY ("ID" )
);
That makes a table "TEMP1" in tablespace C1. Ok so far.
*But it creates the index of the PK in tablespace "pg_default" !!!*
*If I want the index of that constraint in tablespace C1, I must
explicitely specify it.*
Same for any indexes I create: If I do not specify the tablespace when I
create an index, it goes into "pg_default" :-(
And now, this is how I would like Postgres to behave:
a) ALL objects in a given database, not only tables, should by default go
into the tablespace which is defined as default for the DB - in my case
above, "C1".
b) All CREATE commands creating objects into a given database should assume
as the default tablespace the tablespace given as default for the DB, in my
case above "C1".
c) If would be nice if there was an additional (optional) parameter
"INDEX_TABLESPACE" for the CREATE DATABASE command, which would define a
default tablespace to contain indexes for the DB.
d) By extension, it would be nice if there were additional
"_TABLESPACE" parameters for the CREATE DATABASE command for
all object types which have to be stored somewhere in the DB.
In any case, I think that how it behaves now is not 100% correct.
Thanks and best wishes,
RD
Re: [BUGS] BUG #6710: txid_current() provides incorrect txid after server startup
On 27.06.2012 10:08, tar...@gmail.com wrote: The following bug has been logged on the website: Bug reference: 6710 Logged by: Tarvi Pillessaar Email address: tar...@gmail.com PostgreSQL version: 9.1.4 Operating system: linux Description: This happens when epoch is greater than 0. After a checkpoint it starts providing correct txid. It seems that this regression is caused by following commit: 20d98ab6e4110087d1816cd105a40fcc8ce0a307 Correct epoch of txid_current() when executed on a Hot Standby server. When reverting this commit, txid_current() works as expected. Fixed, thanks for the report! -- 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
