Re: [BUGS] BUG #3619: Renaming sequence does not update its 'sequence_name' field
I created this bug report using the pagila db sample (
http://pgfoundry.org/projects/dbsamples/ ). Here is how the sequence and
table are created :
~
CREATE SEQUENCE actor_actor_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
CREATE TABLE actor (
actor_id integer DEFAULT nextval('actor_actor_id_seq'::regclass) NOT
NULL,
first_name character varying(45) NOT NULL,
last_name character varying(45) NOT NULL,
last_update timestamp without time zone DEFAULT now() NOT NULL
);
~
But I made another full trivial test to show this bug from pg8.1 so
using ALTER TABLE request (I don't have access on a pg8.3 presently) :
~
postgres=# create DATABASE br3619;
CREATE DATABASE
postgres=# \c br3619
Vous êtes maintenant connecté à la base de données «br3619».
br3619=# CREATE SEQUENCE sample_seq_to_rename;
CREATE SEQUENCE
br3619=# select sequence_name from sample_seq_to_rename;
sequence_name
--
sample_seq_to_rename
(1 ligne)
br3619=# ALTER TABLE sample_seq_to_rename RENAME TO sample_seq;
ALTER TABLE
br3619=# select sequence_name from sample_seq;
sequence_name
--
sample_seq_to_rename
(1 ligne)
~
--
Guillaume 'ioguix' de Rorthais
Bruce Momjian a écrit :
> I am confused by this bug report. Please show us the CREATE TABLE that
> goes with it.
>
> -------
>
> Guillaume 'ioguix' de Rorthais wrote:
>> The following bug has been logged online:
>>
>> Bug reference: 3619
>> Logged by: Guillaume 'ioguix' de Rorthais
>> Email address: [EMAIL PROTECTED]
>> PostgreSQL version: all - cvs
>> Operating system: Linux
>> Description:Renaming sequence does not update its 'sequence_name'
>> field
>> Details:
>>
>> When renaming a sequence, using ALTER SEQUENCE in pg8.3 or ALTER TABLE for
>> pg < 8.3, its sequence_name field is not updated.
>>
>> Here is how to produce this bug (output from psql 8.3devel):
>> ~
>> pagila=# select sequence_name from actor_actor_id_seq_renamed;
>>sequence_name
>>
>> actor_actor_id_seq
>> (1 row)
>> pagila=# ALTER SEQUENCE actor_actor_id_seq RENAME TO
>> actor_actor_id_seq_renamed;
>> ALTER SEQUENCE
>> pagila=# select sequence_name from actor_actor_id_seq_renamed;
>>sequence_name
>>
>> actor_actor_id_seq
>> (1 row)
>> ~
>>
>> I guess the latest request should output actor_actor_id_seq_renamed,
>> shouldn't it ?
>>
>> --
>> ioguix
>>
>> ---(end of broadcast)---
>> TIP 4: Have you searched our list archives?
>>
>>http://archives.postgresql.org
>
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] BUG #3773: psql segfault on exit
Tom Lane a écrit : "guillaume 'ioguix' de Rorthais" <[EMAIL PROTECTED]> writes: Operating system: macosx 10.4.10 Description:psql segfault on exit If you have any lines longer than 255 bytes in your ~/.psql_history, this is a known bug in Apple's libedit. (They seem to have fixed it in Leopard, btw.) Recommended workaround is to install libreadline from source (the version in /usr/lib isn't really readline) and recompile with that instead. http://archives.postgresql.org/pgsql-hackers/2006-12/msg01222.php regards, tom lane Ok, thanks guys. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #3619: Renaming sequence does not update its 'sequence_name' field
The following bug has been logged online: Bug reference: 3619 Logged by: Guillaume 'ioguix' de Rorthais Email address: [EMAIL PROTECTED] PostgreSQL version: all - cvs Operating system: Linux Description:Renaming sequence does not update its 'sequence_name' field Details: When renaming a sequence, using ALTER SEQUENCE in pg8.3 or ALTER TABLE for pg < 8.3, its sequence_name field is not updated. Here is how to produce this bug (output from psql 8.3devel): ~ pagila=# select sequence_name from actor_actor_id_seq_renamed; sequence_name actor_actor_id_seq (1 row) pagila=# ALTER SEQUENCE actor_actor_id_seq RENAME TO actor_actor_id_seq_renamed; ALTER SEQUENCE pagila=# select sequence_name from actor_actor_id_seq_renamed; sequence_name actor_actor_id_seq (1 row) ~ I guess the latest request should output actor_actor_id_seq_renamed, shouldn't it ? -- ioguix ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3619: Renaming sequence does not update its 'sequence_name' field
Heikki Linnakangas a écrit : > Tom Lane wrote: >> Alvaro Herrera <[EMAIL PROTECTED]> writes: >>> I am amused by the fact that we store the sequence name in the sequence >>> itself though. >> Yeah, it's a bit pointless. One possible response to this gripe would >> be to take the name out of the sequence itself. However, that would >> likely break client-side code for no very good reason. Actually, there's at least one reason : client side code using this value is buggy when a sequence is renamed. That's exactly how I found this issue: when coding "alter sequence" stuff in ppa which was using it... Presently, I will not use this value anymore, but I think other dev / projects which are using it should be inform about this issue. However, I don't know if breaking client side code is the solution neither. > Would it be possible to create a SELECT rule on the sequence that > returns the sequence name from the catalog instead? > Well it looks a pretty good idea to me, but I'm not really aware about internal constraints to judge :S ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #3773: psql segfault on exit
The following bug has been logged online: Bug reference: 3773 Logged by: guillaume 'ioguix' de Rorthais Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3 beta3 Operating system: macosx 10.4.10 Description:psql segfault on exit Details: I just maked/make installed pg8.3b3 from cvs. Here is a copy of the segfault with the psql and backend versions : ~~~ ~)-(Thu 22/Nov/07 18:30) ([EMAIL PROTECTED]:p3:%)- /sw/opt/pg83//bin/psql --version psql (PostgreSQL) 8.3beta3 contains support for command-line editing (~)-(Thu 22/Nov/07 18:30) ([EMAIL PROTECTED]:p3:%)- /sw/opt/pg83//bin/psql -U postgres -p 5433 pagila Password for user postgres: Welcome to psql 8.3beta3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit pagila=# select version(); version PostgreSQL 8.3beta3 on i386-apple-darwin8.10.1, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5367) (1 row) pagila=# \q psql(26486) malloc: *** error for object 0x1806200: incorrect checksum for freed object - object was probably modified after being freed, break at szone_error to debug psql(26486) malloc: *** set a breakpoint in szone_error to debug zsh: segmentation fault /sw/opt/pg83//bin/psql -U postgres -p 5433 pagila ~~~ I don't know which other information I could add. Feel free to ask me for some test/info. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #3774: create table like including index doesn't update pg_constraints with primary key
The following bug has been logged online:
Bug reference: 3774
Logged by: guillaume (ioguix) de Rorthais
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.3 beta3
Operating system: mac os x 10.4.10
Description:create table like including index doesn't update
pg_constraints with primary key
Details:
When creating a table using the "create table ... (like ... inluding
indexes...)" syntaxe, pg_catalog.pg_constraint is not updated with the PK
constraints which actually is setted in pg_index.
Here is my test script :
pagila=# --the original table
\d city
Table "public.city"
Column|Type | Modifiers
-+-+
city_id | integer | not null default
nextval('city_city_id_seq'::regclass)
city| character varying(50) | not null
country_id | smallint| not null
last_update | timestamp without time zone | not null default now()
Indexes:
"city_pkey" PRIMARY KEY, btree (city_id)
"idx_fk_country_id" btree (country_id)
Foreign-key constraints:
"city_country_id_fkey" FOREIGN KEY (country_id) REFERENCES
country(country_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
last_updated BEFORE UPDATE ON city FOR EACH ROW EXECUTE PROCEDURE
last_updated()
pagila=# -- its pk constraint in pg_constraint
SELECT relname,
conname, contype
FROM pg_class cl
JOIN pg_constraint co ON (cl.oid=co.conrelid)
JOIN pg_namespace n ON (cl.relnamespace=n.oid)
WHERE
cl.relname='city' AND n.nspname='public' AND contype='p';
relname | conname | contype
-+---+-
city| city_pkey | p
(1 row)
pagila=# -- create the new table citylike like city
CREATE TABLE
citylike (LIKE city INCLUDING INDEXES INCLUDING DEFAULTS);
CREATE TABLE
pagila=# --the citylike table
\d citylike
Table "public.citylike"
Column|Type | Modifiers
-+-+
city_id | integer | not null default
nextval('city_city_id_seq'::regclass)
city| character varying(50) | not null
country_id | smallint| not null
last_update | timestamp without time zone | not null default now()
Indexes:
"citylike_pkey" PRIMARY KEY, btree (city_id)
"citylike_country_id_key" btree (country_id)
pagila=# -- citylike constraints'
pagila=# SELECT relname, conname, contype
FROM
pg_class cl
JOIN pg_constraint co
ON (cl.oid=co.conrelid)
[BUGS] BUG #3869: A scenario where pg_dump doesn't dump sequence
The following bug has been logged online:
Bug reference: 3869
Logged by: guillaume (ioguix) de Rorthais
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1
Operating system: Linux, MacOSX 10.4.10
Description:A scenario where pg_dump doesn't dump sequence
Details:
Hello,
I think I found a bug in pg_dump from PostgreSQL 8.1.
When creating a table with a SERIAL column, thn alter this column as
smallint, pg_dump doesn't create the sequence anymore.
Which naturaly lead to an error when trying to restore the database.
I can reproduce it under pg 8.0 but not in pg 8.2 and 8.3. Moreover, I had
the oportunity to test it under Linux and MacOSX 10.4.10.
Here the steps to reproduce it :
~
$ sudo su postgres -c '/sw/opt/pg81/bin/psql -p 5431'
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# CREATE DATABASE seq;
CREATE DATABASE
postgres=# \c seq
You are now connected to database "seq".
seq=# CREATE TABLE test (id SERIAL PRIMARY KEY, txt varchar(127) NOT NULL
UNIQUE);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for serial
column "test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey"
for table "test"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_txt_key" for
table "test"
CREATE TABLE
seq=# ALTER TABLE test ALTER id TYPE smallint ;
ALTER TABLE
seq=# \q
$ sudo su postgres -c '/sw/opt/pg81/bin/pg_dump -F c -p 5431 -d seq' >
dump_seq
$ sudo su postgres -c '/sw/opt/pg81/bin/pg_restore -p 5431 -d seq_restore
dump_seq'
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1505; 2604 24586 DEFAULT id
postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"test_id_seq" does not exist
Command was: ALTER TABLE test ALTER COLUMN id SET DEFAULT
nextval('test_id_seq'::regclass);
WARNING: errors ignored on restore: 1
~~~
Here another way to check this bug, grep doesn't find any CREATE SEQUENCE
with pg_dump 8.1. With pg_dump 8.3, it does:
~~~
$ sudo su postgres -c '/sw/opt/pg81/bin/pg_dump -p 5431 -d seq' | grep
'CREATE SEQUENCE'
$ sudo su postgres -c '/sw/opt/pg83/bin/pg_dump -p 5431 -d seq' | grep
'CREATE SEQUENCE'
CREATE SEQUENCE test_id_seq
~~~
--
guillaume (ioguix) de Rorthais
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[BUGS] BUG #5301: difference of behaviour between 8.3 and 8.4 on IS NULL with sub rows of nulls
The following bug has been logged online: Bug reference: 5301 Logged by: Jehan-Guillaume (ioguix) de Rorthais Email address: iog...@free.fr PostgreSQL version: 8.3 Operating system: Linux Description:difference of behaviour between 8.3 and 8.4 on IS NULL with sub rows of nulls Details: Following this discussion on -hacker mailing-list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01828.php ...as I am not sure this issue has been tracked and fixed I open a bug report to keep it in the loop. Please, close this bug report if it was actualy fixed in the meantime. Behaviour in regard to IS NULL with ROWS of NULL values is inconsistent between 8.3 and 8.4. Here is the test case: < $ psql -p 5433 -U postgres -h localhost -c \ 'SELECT substring(version() from 0 for 15), ROW(NULL,NULL) IS NULL, ROW(ROW(NULL),NULL) IS NULL;' substring| ?column? | ?column? +--+-- PostgreSQL 8.3 | t| f (1 ligne) $ psql -p 5434 -U postgres -h localhost -c \ 'SELECT substring(version() from 0 for 15), ROW(NULL,NULL) IS NULL, ROW(ROW(NULL),NULL) IS NULL;' substring| ?column? | ?column? +--+-- PostgreSQL 8.4 | t| t (1 ligne) > and here is another test case where 8.3 is inconsistent with *himself* this time: < postgres=# SELECT substring(version(),12,5); substring --- 8.3.9 (1 ligne) postgres=# SELECT ROW(ROW(NULL),NULL) IS NULL FROM (SELECT 1) t; ?column? -- t (1 ligne) postgres=# SELECT ROW(ROW(NULL),NULL) IS NULL; ?column? -- f (1 ligne) > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] ERROR: failed to build any 3-way joins
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, A user reported us the following bug under phpPgAdmin with 9.0beta1, but it seems it comes from backend. Here is how to reproduce the bug: > postgres=# SELECT version(); version - -- PostgreSQL 9.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (Debian 4.4.3-2) 4.4.3, 32-bit (1 row) postgres=# SELECT rolname FROM pg_catalog.pg_roles AS r, pg_auth_members AS m WHERE r.oid=m.roleid AND member IN ( SELECT oid FROM pg_catalog.pg_roles WHERE rolname='postgres') ORDER BY rolname; ERROR: failed to build any 3-way joins < - -- Jehan-Guillaume (ioguix) de Rorthais DBA http://www.dalibo.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkv5i+AACgkQXu9L1HbaT6LRNgCgxD2+d7V/+CPLdXdqoLLyQCF5 CdsAn0grUp3bXjOazRz111wTJDxa4ff6 =9qaz -END PGP SIGNATURE- -- 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 #6051: wCTE query fail with wrong error text on a table with rules
The following bug has been logged online: Bug reference: 6051 Logged by: Jehan-Guillaume (ioguix) de Rorthais Email address: j...@dalibo.com PostgreSQL version: 9.1beta1 Operating system: Linux x86_64 Description:wCTE query fail with wrong error text on a table with rules Details: While testing wCTE, I tried to empty a parent table, feeding its child using rules with this kind of query: WITH t1 AS ( DELETE FROM ONLY test RETURNING * ) INSERT INTO test SELECT * FROM t1; It works perfectly on a table without rules, but fail with what seems like a non related error message if there is a rule on INSERT on this table: test=# WITH t1 AS ( DELETE FROM ONLY test RETURNING * ) INSERT INTO test SELECT * FROM t1; ERROR: could not find CTE "t1" I was expecting this query either to work or raise a comprehensive error message. Here is a simple script to reproduce this behaviour: ==> $ createdb test $ psql test psql (9.1beta1) Type "help" for help. test=# SELECT version(); version PostgreSQL 9.1beta1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.0 20110513 (prerelease), 64-bit (1 row) test=# CREATE table test AS select i from generate_series(1,3) as t(i); SELECT 3 test=# SELECT * FROM test; i --- 1 2 3 (3 rows) test=# WITH t1 AS ( test(# DELETE FROM ONLY test RETURNING * test(# ) test-# INSERT INTO test SELECT * FROM t1; INSERT 0 3 test=# SELECT * FROM test; i --- 1 2 3 (3 rows) test=# CREATE TABLE test2 () inherits (test); CREATE TABLE test=# CREATE RULE test_ins AS ON INSERT TO test DO INSTEAD INSERT INTO test2 VALUES (NEW.i); CREATE RULE test=# WITH t1 AS ( DELETE FROM ONLY test RETURNING * ) INSERT INTO test SELECT * FROM t1; ERROR: could not find CTE "t1" <== -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
