Re: [BUGS] BUG #4380: Comparison of OLD and NEW columns in trigger does not always work
On Wed, 27 Aug 2008, Daryl Joubert wrote: > > The following bug has been logged online: > > Bug reference: 4380 > Logged by: Daryl Joubert > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.3.3 > Operating system: WinXP SP2 > Description:Comparison of OLD and NEW columns in trigger does not > always work > Details: > > When comparing NEW.column_name to OLD.column_name in 'PG_UPDATE' operation > in a trigger, I get unpredictable results. Use the following script to > create the "Business Partner" table and its trigger, then add a few rows of > data to "First Name", "Middle Name" and "Last Name" using pgAdmin, and watch > how "Name" is built up for the former 3 columns. > > Then: change the "Middle Name" column of an existing row repeatedly and you > should see that sometimes "Name" is updated, sometimes not. I can repeat the > problem here within 5 to 10 changes of "Middle Name". Am I doing something > wrong? Here is the script: Well, the function does the wrong thing if you set a field to NULL or change a field that was previously NULL to a non-NULL value since a <> NULL is unknown not true. And once that happens you might need to do a couple of updates to get out of the odd state. Is it possible that the updates that are failing fit that pattern? If so, using IS DISTINCT FROM rather than <> may work. -- 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 #4412: Check constraints cannot be added to the table for fields that are mixed case
On Tue, 9 Sep 2008, Kevin wrote:
> The following bug has been logged online:
>
> Bug reference: 4412
> Logged by: Kevin
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 8.0.15
> Operating system: Gentoo Linux
> Description:Check constraints cannot be added to the table for
> fields that are mixed case
> Details:
>
> Check constraints cannot be added to the table for fields that are mixed
> case.
>
> Example - field employeeName in table Employees
> -
> ALTER TABLE "Employees" ADD CONSTRAINT "employeeNameTest" CHECK
> (employeeName != 'Kevin')
I don't have 8.0 around to test, but I think that should be
("employeeName" != 'Kevin'). If you created the table with mixed-case
columns in double quotes or used a tool which double quoted them for you,
then you must double quote the column name when it's referenced, otherwise
the names are case-folded.
--
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 #4640: Drop leading zero in EXECUTE
On Thu, 5 Feb 2009, Eduard Deacoon wrote:
> For example:
> --- Function convert column to string with delimiter
> --- $1 - TABLE with COLUMN to convert
> --- $2 - COLUMN to convert
> --- $3 - COLUMN for WHERE CLAUSE
> --- $4 - WHERE value
> --- $5 - delimeter
> --- In fact: SELECT $2 FROM $1 WHERE $3 = $4 returning as string with
> delimiter $5
> CREATE OR REPLACE FUNCTION column_to_string(TEXT, TEXT, TEXT, TEXT, TEXT)
> RETURNS TEXT AS
> E'
> DECLARE
> string_res TEXT := NULL;
> r RECORD;
> BEGIN
> FOR r IN EXECUTE ''SELECT '' || QUOTE_IDENT($2) || '' AS t '' || ''
> FROM '' || QUOTE_IDENT($1) || ''
> WHERE '' || QUOTE_IDENT($3) || '' = '' ||
> $4||''::TEXT'' ||
> '' ORDER BY '' || QUOTE_IDENT($2) || '' ASC''
The output of that is going to look something like
SELECT "Valueof$2" AS t FROM "Valueof$1" WHERE "Valueof$3" =
Valueof$4::text ORDER BY "Valueof$2" ASC.
So, given say a call with ('a', 'b', 'c', '003', ',') you'll get
SELECT "b" AS t FROM "a" WHERE "c" = 003::text ORDER BY "b" ASC
In that case, when executed the 003 is going to be treated as a number
(and thus is the same as 3). If you wanted the 003 to be treated as a
string literal in the string to be executed, you need to quote it,
preferably with quote_literal.
--
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 #5113: Postgres not scanning indexes
On Tue, 13 Oct 2009, dan wrote: > Let's say I have a table t with 5 columns c1 NOT NULL, c2 NOT NULL, c3, c4, > c5 > and I have a UNIQUE index on (c1, c2) (remember c1 and c2 have a not null > constraint) > > When I run the query: > select c1,c2 from t > > I expect the explain to say index scan; instead it says table scan. > The index has ALL the info I need and in my case is about 2% the size of the > table. The index is missing the visibility information, so right now the system cannot run such a query over the index alone since it couldn't determine if the row version is visible to your statement. That data is currently in with the data in the table, so it ends up needing to go back and read the rows from the main table. IIRC, there's been talk and some work around supporting indexes with visibility info, but I think there might have been some issues that needed to be worked out before that would work. -- 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 #5182: query with deferents results
On Thu, 12 Nov 2009, artur saldanha wrote: > > The following bug has been logged online: > > Bug reference: 5182 > Logged by: artur saldanha > Email address: artur.salda...@gmail.com > PostgreSQL version: 8.3.5 > Operating system: Fedora 64 > Description:query with deferents results > Details: > > PostgreSQL 8.3.5 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.3.2 > 20081007 (Red Hat 4.3.2-6 > this query presents 7 rows... > SELECT * >FROM ( (SELECT n.nrnota, i.cdproduto, i.qtde as batata, i.vrpunit, > n.cdfinalidade, cdnrnota >FROM nfiscais n > JOIN itnfiscais i ON n.cnnota = i.cnnota > WHERE n.cdfinalidade = '5A7'::bpchar AND n.status = '131'::bpchar) > UNION > (SELECT n.nrnotaorigem AS nrnota, i.cdproduto, i.qtde * > (-1)::numeric AS batata, i.vrpunit, n.cdfinalidade, cdnrnota >FROM nfiscais n > JOIN itnfiscais i ON n.cnnota = i.cnnota > WHERE n.cdfinalidade = '5A8'::bpchar AND n.status = '131'::bpchar)) q > where nrnota = '009426' > > same query that omit a colum cdnrnota present only 3 rows.. > the rows omitted are in second query and rows omitted are equal contents. Are the cdnrnota values different for those rows? UNION removes duplicates even of rows from the same side, so that might have something to do with the behavior you're seeing. A complete report with table definitions, sample data and results would probably be helpful. -- 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] INSERT/SELECT with ORDER BY
Unless I'm horribly misreading the spec, this is correct. ORDER BY is used in reference to cursor definitions and direct select statements, but is not part of query expressions which is what the INSERT INTO references. ::= INSERT INTO ::= [] | DEFAULT VALUES ::= But, this may be a useful extension to allow, at least with the existance of LIMIT. Stephan Szabo [EMAIL PROTECTED] On Thu, 17 Aug 2000, Bob Rivoir wrote: > > Please enter a FULL description of your problem: > > I got the following error message when I tried to do an INSERT using > SELECT ... ORDER BY: > > ERROR: ORDER BY is not allowed in INSERT/SELECT
Re: [BUGS] PostgreSQL BugTool Submission
Yes, this is a known issue due to the fact that the triggers use SPI and need to use SELECT ... FOR UPDATE to lock the rows it is reading (and select for update requires the update permission). The workaround I know about for now is to give update permission and make triggers to disallow updates as appropriate. (If this isn't in the TODO list or FAQ yet, it probably should be.) Stephan Szabo [EMAIL PROTECTED] On Wed, 23 Aug 2000 [EMAIL PROTECTED] wrote: > NAGY Andras ([EMAIL PROTECTED]) reports a bug with a severity of 3 > The lower the number the more severe it is. > > Short Description > Foreign keys referencing read-only tables fail
Re: [BUGS] PostgreSQL BugTool Submission
On Tue, 22 Aug 2000 [EMAIL PROTECTED] wrote: > James Aspnes ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > checking foreign keys requires write access > > Long Description > In version 7.0.2, create tables A and B where B has a foreign > key reference to A. Grant user X insert access on B but only > select access on A. Attempts by X to insert into B will fail > with an access error on A, even though X is allowed to read A > (and thus verify that the foreign key constraint is not violated.)[B Yes. This is a known issue due to getting row level locks on rows in A with SELECT ... FOR UPDATE which requires update permission (and presumably select as well). For more information, check out the -hackers archive over the last couple of months. The current workaround is somewhat painful, and involves giving update permission and using a trigger to actually prevent unwanted changes.
Re: [BUGS] table aliases with updates
This sounds like an enhancement, not a bug per-se. SQL92 seems to say the below about searched update statements. What was the error you got from your first query? (I'd guess a parser error at t1?) ::= UPDATE SET [ WHERE ] Stephan Szabo [EMAIL PROTECTED] On Tue, 29 Aug 2000 [EMAIL PROTECTED] wrote: > andrew brown ([EMAIL PROTECTED]) reports a bug with a severity of 3 > The lower the number the more severe it is. > > Short Description > table aliases with updates > > Long Description > It appears that table aliases cannot be used in update queries for the table we are >updating. > > Sample Code > This doesn't work: > > update table1 t1 set value1 = (select value1 from table2 t2 where t2.value2 = >t1.value2) > > But this does: > > update table1 set value1 = (select value1 from table2 t2 where t2.value2 = >table1.value2)
Re: [BUGS] referential integrity requires write permission to atable which only needs to be read
On Wed, 30 Aug 2000 [EMAIL PROTECTED] wrote: > Short Description > referential integrity requires write permission to a table which only needs to be >read Yes, this is a known problem. > And, why is the trigger trying to select WITH UPDATE? (locking?) Yes, if you don't grab a row lock, another transaction can come along and delete the row you're referencing after you've checked it, but before that transaction sees that you've added a referencing row (which could lead to orphened children) > In any case, this is requiring us to grant update permission to this > user group, and we really only want them to have select permission to > the table in question. Right now, the suggested workaround is to do a trigger before updates that prevents the unwanted writes in plpgsql. Not particularly good, but functional until this gets fixed (and I don't know precisely when that'll be)
Re: [BUGS] bugs
Yes, right now references constraints require update
permissions on the table being referenced in order
to grab row level locks on it with SELECT FOR UPDATE.
Stephan Szabo
[EMAIL PROTECTED]
On Thu, 31 Aug 2000, Alexei E. Korneyev wrote:
>
> POSTGRESQL BUG REPORT TEMPLATE
>
>
>
> Your name : Alexei E. Korneyev
> Your email address : [EMAIL PROTECTED]
>
>
> System Configuration
> -
> Architecture : Intel Pentium II
>
> Operating System : Linux RedHat 2.2.12
>
> PostgreSQL version : PostgreSQL-7.0.2
>
> Compiler used : uncnown Build Host: utility.wgcr.org
>
>
> Please enter a FULL description of your problem:
>
> i create this:
>
>
> create table "users" (
> namevarchar(10) PRIMARY KEY
> );
> create table "stat" (
> peernamevarchar(10) REFERENCES users(name)
> );
>
> revoke ALL on stat,users from PUBLIC;
> grant INSERT, SELECT on stat to statist;
> grant select on users to statist;
> grant ALL on stat to basemaster;
> insert into users values('niva');
> insert into stat values ('niva',abstime('now'),timespan(0),8607272,1829436);
> -
> After that I do(make) connection as the user statis and execute cmd:
> ---
> insert into stat
> values('niva',abstime('now'),timespan(abstime('now')-(select max(timewrite)
> from stat)),50,50);";
> --
> server say:Access deny on users...
>
> After I change the rights on:
>
> grant select,update on users to statist;
>
> ,the query exec successfully
>
>
> Alexei E. Korneyev
> [EMAIL PROTECTED]
>
Re: [BUGS] Possible bug in referential integrity system
Actually, this is what the spec defines. You're using match unspecified, which means: - If no was specified then, for each row R1 of the referencing table, either at least one of the values of the referencing columns in R1 shall be a null value, or the value of each referencing column in R1 shall be equal to the value of the corresponding referenced column in some row of the referenced table. Stephan Szabo [EMAIL PROTECTED] On Sat, 2 Sep 2000, Richard Ellis wrote: > The submit button on the form located at > http://www.postgresql.org/bugs/bugs.php?1 results in a report of a > parsing error, so I'm sending this here as the alternative. > > Is the following a bug in the referential integrity system? This is > for PG 7.0.0. I realize that declaring the "ref" column in t2 > additionally as "not null" would prevent this. However, why does the > "references" check allow insertion of a null value into t2 when there > are no corresponding null value in the num column of t1? > > If this is actually fixed in 7.0.2, then please accept my apologies.
Re: [BUGS] Backend crash trying to delete rows
This is a known bug. Current sources will not crash, but will
instead fail the query (since they are still referencing the name).
By 7.2, the ri triggers should probably be following the oid
of the tables and columns and will be safe from renames.
Stephan Szabo
[EMAIL PROTECTED]
On Sat, 9 Sep 2000 [EMAIL PROTECTED] wrote:
> Andrea Aime ([EMAIL PROTECTED]) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> Backend crash trying to delete rows
>
> Long Description
> Platform: RedHat 6.2, PostgreSQL 7.02 compiled by gcc egcs 2.91.66,
> running on a PIII 733Mhz, 128 MB RAM, 9 GB SCSI HD.
>
> Create an empty database and then two table, a master and a slave,
> slave references master primary key with an on delete cascade
> fk option. Everything runs fine, when you delete rows in master
> table you get also corresponding slave rows deleted. Now rename
> slave into slave2 and retry delete: backend crashes.
> FK are part of table structure, you can implement them as trigger
> but they must follow any table modification, otherwise schema
> evolution is not possible.
>
> Sample Code
> I put here some code just to give you an idea, I haven't
> tried it.
>
> create table master(
> id_master serial,
> name varchar,
> primary key(id_master)
> );
>
> create table slave(
> id_slave serial,
> id_master int4,
> primary key(id_slave),
> foreign key(id_master) references master(id_master) on delete cascade
> );
>
> insert into master(name) values('Andrew');
> insert into slave(id_master,slave) values(1,'Rod');
>
> #this one works fine
> delete from master;
>
> alter table slave rename to slave2;
>
> insert into master(name) values('Andrew');
> insert into slave(id_master,slave) values(2,'Rod');
>
> #boom!
> delete from master;
Re: [BUGS] [Fwd: [Fwd: named constraints]]
IIRC, ALTER TABLE ... DROP CONSTRAINT isn't implemented yet, so that's going to fail in any case. ADD CONSTRAINT is implemented for foreign keys (in 7.0) and should be implemented for check constraints in 7.1. Stephan Szabo [EMAIL PROTECTED]
Re: [BUGS] Regular expression not working [^xyz]
On Mon, 2 Oct 2000, Stuart Peters wrote: > \connect - postgres > CREATE TABLE "bug" ( > "title" text > ); > COPY "bug" FROM stdin; > abcdefghijklm > nopqrstuvwxyz > aeiou > \. > > > > The following queries work as expected, EXCEPT for [2]. I believe > that [2] and [3] should return identical results, but [2] simply > lists ALL titles: > > [1] select title from bug where title ~* '[ae]'; > >Lists titles that contain one of the letters a or e: WORKS > > [2] select title from bug where title ~* '[^ae]'; > >Lists ALL titles rather than those that don't contain a or e: > DOESN'T WORK >(Note: 'select title from bug where title ~* '[^a];' does not work either No, that will match any title with at least one character that is not an a or e. It you want a match for all characters not a or e it's ~* '^[^ae]*$' If you were to put into bug a row like a or ae or e, that wouldn't match on 2, but abcde... would match because there exists a character other than a or e.
Re: [BUGS] Possible bug with permissions. (Table A has select ,insert, update rights but has a reference to a second table that only hasselect rights)
It's been discussed a fair bit in the mailing lists but I don't think it's made the documentation. The triggers use SELECT FOR UPDATE to get the row locks but that forces the update permission need. In 7.1 this should be gone (Peter E had a patch to remove the most common cases of this). Stephan Szabo [EMAIL PROTECTED] On Fri, 6 Oct 2000, Wendel Leibe wrote: > I think I have found a bug with Postgres' permissions. I have combed > through the documentation, but I haven't been able to find an explanation > for the following problem. > > First of all here is what I am running. > Postgress version 7.0.2 , > OS Linux Red Hat 6.2 > Kernel version 2.2.17 >
Re: [BUGS] Referencial integrity when there are timestamp primarykeys
This is a stupid garden variety bug and I'm not sure why I didn't catch
it previously. The patch included is against fairly current sources, but
it may apply cleanly against 7.0.2 as well.
On Fri, 6 Oct 2000, Vilson farias wrote:
> I found a irregular behavior with constraints.
>
> I can only set a referencial integrity between these tables when there are
> no data, even if there are no change to referential integrity violation.
*** pgsql.old/src/backend/commands/command.cTue Sep 12 14:06:47 2000
--- pgsql/src/backend/commands/command.cTue Oct 10 12:15:13 2000
***
*** 1388,1402
{
Ident *fk_at = lfirst(list);
! trig.tgargs[count++] = fk_at->name;
}
foreach(list, fkconstraint->pk_attrs)
{
Ident *pk_at = lfirst(list);
! trig.tgargs[count++] = pk_at->name;
}
! trig.tgnargs = count;
scan = heap_beginscan(rel, false, SnapshotNow, 0, NULL);
AssertState(scan != NULL);
--- 1388,1405
{
Ident *fk_at = lfirst(list);
! trig.tgargs[count] = fk_at->name;
! count+=2;
}
+ count = 5;
foreach(list, fkconstraint->pk_attrs)
{
Ident *pk_at = lfirst(list);
! trig.tgargs[count] = pk_at->name;
! count+=2;
}
! trig.tgnargs = count-1;
scan = heap_beginscan(rel, false, SnapshotNow, 0, NULL);
AssertState(scan != NULL);
Re: [BUGS] PostgreSQL BugTool Submission
Actually Peter did a patch for this fairly recently I believe. I haven't grabbed CVS recently enough to know if it got committed. There's a related question of what permissions you need to follow referential actions (currently it's the same permission as if you were doing the implied statement). Stephan Szabo [EMAIL PROTECTED] On Sat, 14 Oct 2000, Bruce Momjian wrote: > Can someone give me a good description of this for TODO? > > > > > Yes, this is a known issue due to the fact that the > > triggers use SPI and need to use SELECT ... FOR UPDATE > > to lock the rows it is reading (and select for update > > requires the update permission). The workaround I > > know about for now is to give update permission and make > > triggers to disallow updates as appropriate. > > > > (If this isn't in the TODO list or FAQ yet, it probably > > should be.) > > > > Stephan Szabo > > [EMAIL PROTECTED] > > > > On Wed, 23 Aug 2000 [EMAIL PROTECTED] wrote: > > > > > NAGY Andras ([EMAIL PROTECTED]) reports a bug with a severity of 3 > > > The lower the number the more severe it is. > > > > > > Short Description > > > Foreign keys referencing read-only tables fail > > > > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 >
Re: [BUGS] PostgreSQL BugTool Submission
Well, actually the question of whether failing referential actions due to permission deficits of the user doing the delete/update on the pk table is a bug or feature still stands. It would be fairly trivial to extend Peter's patch to effectively setuid on the actions, but the question is whether or not that's useful and correct. Stephan Szabo [EMAIL PROTECTED] On Sat, 14 Oct 2000, Bruce Momjian wrote: > Oh, OK. I will forget it. > > > > > Actually Peter did a patch for this fairly recently I > > believe. I haven't grabbed CVS recently enough to know > > if it got committed. There's a related question of what > > permissions you need to follow referential actions (currently > > it's the same permission as if you were doing the implied > > statement).
Re: [BUGS] Select where (upper(xy)~'.CH'); ..matches also SPACE CH
I think that's what it should be doing. ~ is a regexp search, and . is the any character match special character. If you're looking for an actual . you'll need to double backslash escape it. Stephan Szabo [EMAIL PROTECTED] On Thu, 26 Oct 2000 [EMAIL PROTECTED] wrote: > Marcel ([EMAIL PROTECTED]) reports a bug with a severity of 3 > The lower the number the more severe it is. > > Short Description > Select where (upper(xy)~'.CH'); ..matches also SPACE CH > > Long Description > There exists a problem with the ~ statement. The codesample and the text is from an >adult contact database. > > The compare string .CH matches the word 'EINEN CHANCE' in the sentence. Seems to be, >that the . will match the space between the words. This doesn't happen, if you >replace .CH with _CH or something else. > > Sample Code > meetingpoint=> select upper(adtext) from ads where adnumber='40ac066e1db0633a' and >(upper(adtext)~'.CH'); > upper > > >-- > >-- > - > WELCHE DOMINA WILL SICH EINEN SKLAVEN HALTEN. BIN EIN 29-JäHRIGER]BI-BOY UND SEHR >DEVOT. BITTE GEB > EN SIE MIR EINEN CHANCE DAMIT ICH IHNEN]BEWEISEN KANN DASS ICH IHR SKLAVE SEIN WERDE >UND NUR FüR S > IE BEREIT BIN]ALLES ZU MACHEN WAS SIE VON MIR VERLANGEN.]UNTERTäNIGST IHR SKLAVE] > (1 row) > > meetingpoint=> select upper(adtext) from ads where adnumber='40ac066e1db0633a' and >(upper(adtext)~'_CH'); > > upper > - > (0 rows) > > meetingpoint=> > > No file was uploaded with this report >
Re: [BUGS] Problem with group by command
On Fri, 27 Oct 2000, Jessica Ord wrote: > I would be grateful if any of you could provide me any information that you > may have. > > I have written a perl program which runs fine on a machine that was using > PostgreSQL 6.4.2. I setup another machine which was running PostgreSQL > 6.5.3 bundled by the Linux 6.2. I used the copy command to migrate most of > the tables on the new machine and re-run the same program. You'll probably be best off getting the 7.0.x rpms and using those instead of 6.5.x (see below). > The same error message keeps on appear said that: > Invalid use of aggregates or non-group column in target list. > > The SQL statement used is: > select username, user_index, date, status from subscribers, cellnet_mesg_log > where status != 3 and date >= '25/10/2000' and date < '01/11/2000' and > cellnet_mesg_log.user_index = subscribers.oid group by username; Well, I don't believe that's a legal SQL statement. All columns in the select list of a group by query must either be grouped or inside a set value function (min, max, etc...). I think old versions of postgres got this wrong, so you may be seeing fallout from that. Fundamentally, *which* value of date and status would you want to give if there was more than one for a given username? If there's only one, using group by is useless, if there's more than one, the query gives indeterminate results. Under 7.0.x you can use distinct on and order by to give repeatable results (if you order by all the fields, and use distinct on (username) I believe you'd get the first/last row by the ordering criteria that matched each username)
Re: [BUGS] MySQL/PostgreSQL discrepancy
> THE TEST
> Run 'psql', then enter the following except for the select statement
> output.
>
> ---BEGIN---
> create table test (name char, a int, b int);
> insert into test values ('x', 1, 2);
> select * from test;
> update test set a=3,b=a where name='x';
> insert into test values ('y', 1, 2);
> update test set b=a,a=3 where name='y';
> select * from test;
> name | a | b
> --+---+---
> x| 3 | 1
> y| 3 | 1
> drop table test;
> ---END---
>
> I am used to MySQL so I expected the following from the select
> statement.
> name | a | b
> --+---+---
> x| 3 | 3
> y| 3 | 1
>
> Which behavior is the correct?
I believe ours...
>From update positioned:
"The s are effecitvely evaluated before
updating the object row. If a contains a
reference to a column of T, then the reference is to the value
of that column in the object row before any value of the object
row is updated."
>From update searched:
"The are effectively evaluated for each row
of T before updating any row of T."
Re: [BUGS] Core dump when connecting to database
Where are your postmaster logs going? (where is your startup redirecting stdout and stderr). Those may have additional information. Also, can you get a backtrace from the core file with gdb? On Mon, 30 Oct 2000, S Shaffer wrote: > Post for FREE via your newsreader at post.usenet.com > > Pgsql Users, > > I get the following message when trying to connect to database using psql or > the ODBC driver. > > Connection to database 'warehouse' failed. > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > > and also a core dump in the 'warehouse' database directory. I can connect to > other databases normally. > > I think the problem started when an admin user was creating a new view & had > trouble. > > Can anyone help me with the syntax to get the postmaster to log more > information on the error to a file? > > Is there any other way to get in & try to identify & fix the problems? > > Thanks for any help you can give me,
Re: [BUGS] bug
This is a known problem in 7.0.x. The insert/update constrained table case should be fixed in 7.1. You can get details about it in the mailing list archives. Stephan Szabo [EMAIL PROTECTED] On Tue, 31 Oct 2000, David Orr wrote: > Your name : Dave Orr > Your email address: [EMAIL PROTECTED] > > > System Configuration > - > Architecture (example: Intel Pentium) :Intel Pentium > > Operating System (example: Linux 2.0.26 ELF):Linux 2-2.14-lmdklinus > > PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.2 > > Compiler used (example: gcc 2.8.0) :gcc 2.95.2 > > > Please enter a FULL description of your problem: > > > I create two tables where a field in one is constrained to only contain values > that exist in a field in the other. I create a group and grant all on the > constrained table to that group. I grant the group select on the constraining > table. I add a user to the group. When the user tries to insert into the > constrained table, I get permission denied. > > I need to grant update on the constraining table in order to insert a record > into the constrained table > > > Please describe a way to repeat the problem. Please try to provide a > concise reproducible example, if at all possible: > -- > Create a database named test. Run this script into it: > > create table by_me( > needed int primary key > ); > insert into by_me values(1); > > create table im_constrained( > field1 int, > > constraint im_constrained_by_me foreign key ( field1) > references by_me > match full > on delete no action > ); > > create group test; > grant select,update,delete,insert on im_constrained to group test; > grant select on by_me to group test; > > Add a user to group "test" > suid to that user and try to add a record > > test=> insert into im_constrained values(1); > ERROR: by_me: Permission denied. > > If you know how this problem might be fixed, list the solution below: > - >
Re: [BUGS] FOREIGN KEY check needs UPDATE permission
Known issue with no real workaround apart from giving update permissions. Should be fixed for 7.1 if Peter Eisentraut's patch is in.
Re: [BUGS] Both cross-named & compound foreign key constaints fail
On Mon, 27 Nov 2000 [EMAIL PROTECTED] wrote: Won't have a full answer until I'm home, but figured I'd send something. > Description: Run the psql script below to generate > the following error: > psql:bug.sql:54: ERROR: > constraint : > table al_addresses_data does not have > an attribute address_press_id > > Problem: The failing command is ALTER TABLE > al_presses ADD CONSTRAINT ... FOREIGN KEY > ... *REFERENCES* al_addresses_data > (record_id, *press_id*). > PostgreSQL\'s error message indicates that > the DB is looking for an al_addresses_data > attribute with the *same* name as the > foreign key column in source table (i.e. > al_presses.address_press_id). It *appears* > that the REFERENCES list is being ignored > (with respect to at least the second element > of the REFERENCES list). > Minor Problem: If the tables are left empty, the schema > creates without error even though a latent > error exists! Comment out the two insert > statements to witness error free schema > construction. This indicates that some > DDL/schema-creation time validation is > being deferred. It would be far more > helpful to detect all schema errors > during schema construction. A small point. Okay, these two say to me that it's almost certainly not the ALTER TABLE code that's generating the message, but instead the trigger itself (the ALTER TABLE calls the trigger for each row already in the table). Does it fail if you do an insert after getting a successful create in the second case, or does it work there? I'm wondering if I'm passing in bogus arguments to the trigger function in the data check in certain cases. 7.1 should do column name checking at constraint creation time, but I'm not sure if the other issue is fixed. I'll check my 7.1 and 7.0 machine with sources when I get home. :) > Minor Problem: The ALTER TABLE ... ADD CONSTRAINT command > produces an error message about an > constraint. The failed constraint *DOES* > have a given name. A small point - but a > potential source of confusion. This one should be fixed in 7.1, I miscopied something from somewhere else and didn't get the constraint name to the trigger. > Aside: Trying to work around this bug with an > al_addresses_data view that maps record_id > to address_id and press_id to address_press_id > also fails. This is a very minor feature/function > issue once the direct al_addresses_data > constraint works. Still, it would seem that > a clever implementation of views might hide the > table versus view distinction from the > referential integrity logic ... and thus > make this work by default. At present, > this failing view work-around produces the > following error when the ALTER TABLE is attempted: > ERROR: system column oid not available - > al_addresses is a view > Of course, I'm way over my head on this point > since I know nothing of PostgreSQL's internals ... Unfortunately you can not currently constrain views that way. The reason not much has been done about it is related to the check constraints with subselects. You may need to actually constrain all tables referenced by the view in order to make the constraint work and that's a bigger problem, especially if the view has set value functions, etc. (An insert to a table could cause say a count value say to change which would orphan a row in the fk table)
Re: [BUGS] Both cross-named & compound foreign key constaints fail
Okay. On current sources, this seems to work with only a few changes. You need unique or primary key constraints on the columns being referenced (this is part of the spec but was not checked in 7.0) A couple of other things, currently constraints don't inherit very well. So, you'd probably want to have the fk constraint on al_ids on al_addresses_data as well and the unique constraints need to be on the targets of the fk constraints explicitly. > Sample Code > CREATE TABLE al_descs ( > name VARCHAR(84) NOT NULL, > name_sort VARCHAR(84) NOT NULL, > name_tsTIMESTAMPNOT NULL DEFAULT CURRENT_TIMESTAMP, > descriptionVARCHAR(256) NOT NULL DEFAULT 'No description is >available.', > explanationTEXT NOT NULL DEFAULT 'No explanation is >available.', > priority INT4 NOT NULL DEFAULT 1, > secondary BOOL NOT NULL DEFAULT TRUE ) ; > > /* A press is like a server farm/cluster */ > CREATE TABLE al_presses ( > record_id INT4 NOT NULL, > address_id INT4 NOT NULL DEFAULT 3, > address_press_id INT4 NOT NULL DEFAULT 3 ) > INHERITS ( al_descs ) ; > > INSERT INTO al_presses (record_id, name, name_sort) VALUES (1, 'Foo', 'foo') ; > > /* Most entities have a compound internal/logical identifer ... > The local server farm/cluster identifier and the server farm/cluster id */ > CREATE TABLE al_ids ( > record_idINT4 NOT NULL, > press_id INT4 NOT NULL DEFAULT 1, > CONSTRAINT al_ids_presses_fk > FOREIGN KEY ( press_id ) > REFERENCES al_presses ( record_id ) > MATCH FULL ON DELETE RESTRICT ON UPDATE RESTRICT > DEFERRABLE INITIALLY DEFERRED ) ; > > CREATE TABLE al_addresses_data ( > fictional BOOLNOT NULL DEFAULT FALSE, > verified BOOLNOT NULL DEFAULT FALSE, > street_number VARCHAR(16) NOT NULL DEFAULT '', > street_directional VARCHAR(2) NOT NULL DEFAULT '', > street_nameVARCHAR(32) NOT NULL DEFAULT '', > street_suffix VARCHAR(12) NOT NULL DEFAULT '' ) > INHERITS ( al_ids ) ; > > INSERT INTO al_addresses_data (record_id, press_id) VALUES ( 3, 3 ) ; > > ALTER TABLE al_presses ADD > CONSTRAINT al_presses_address_data_fk > FOREIGN KEY (address_id, address_press_id) > REFERENCES al_addresses_data (record_id, press_id) > MATCH FULL > ON DELETE RESTRICT ON UPDATE RESTRICT > DEFERRABLE INITIALLY DEFERRED ; > > DROP TABLE al_addresses_data ; > > DROP TABLE al_presses ; > > DROP TABLE al_ids ; > > DROP TABLE al_descs ; > > > > No file was uploaded with this report >
Re: [BUGS] constrains of array
> Hi.
>
> I use version from cvs.
>
> I want to have array of referencies to another table, so I do:
> cms=# create table a (a int primary key); create table b (b int[]
> references a);
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for
> table 'a'
> CREATE
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
>
> But now I can't add anything to table b:
> cms=# insert into a values (1);
> INSERT 52069 1
> cms=# insert into b values ('{1}');
> ERROR: Unable to identify an operator '=' for types 'int4' and '_int4'
> You will have to retype this query using an explicit cast
>
> I expect either of following:
> 1) REFERENCIES from array to scalar should create correct trigger (check
> every entity of array)
Actually, no. Technically, it should be seeing if the exact same array is
on the other side. AFAIK, There is no definition of the RI constraint
in the spec that works that way. We've talked about implementing such
a beast as an extension, but there are some issues about indexing that
we need to look at before we can do that in general.
> 2) It should be error in *creation* of table if there is no comparasion
> operator for constrain check
Possibly, although it currently doesn't to allow you to add the operator
after you do the references. The benefits of that might be outweighed by
the problems if you don't add the operator.
> 3) Error in insert should say something about trigger, otherwise user have
> to guess what is wrong with his query.
Definately true, but unfortunately i'm not sure if it's possible in the
current implementation (it does another query inside the trigger, and
that's what's erroring -- I don't think it knows it's in the trigger at
that time).
Re: [BUGS] constrains of array
On Tue, 12 Dec 2000, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > >> 2) It should be error in *creation* of table if there is no comparasion > >> operator for constrain check > > > Possibly, although it currently doesn't to allow you to add the operator > > after you do the references. The benefits of that might be outweighed by > > the problems if you don't add the operator. > > I can't see any good reason not to require the operator to pre-exist. The only case I could see would be if there was some case where you had equality operators that needed to be defined after the table that had the references constraint (not sure if that could ever happen). You could use alter table in these cases though. > In fact, there's a good argument that we should require the two columns > to have the exact same datatype. Otherwise, equality may be a pretty > fuzzy concept. Think about varchar vs bpchar comparison, for example > --- shall we consider trailing blanks significant? Which column will > drive the choice? I think the spec only requires them to be comparable I believe (I'd assume that the match predicate rules would apply), so would an equality operator be sufficient to tell that?
Re: [BUGS] constrains of array
On Tue, 12 Dec 2000, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > >> In fact, there's a good argument that we should require the two columns > >> to have the exact same datatype. > > > I think the spec only requires them to be comparable > > Oh, in that case never mind ... but I'd still favor checking for > existence of the comparison operator right away. Okay, will do. :)
Re: [BUGS] Outer joins aren't working with views
What version are you using? The sample code works for me on current sources, three rows with the last one as 3|null|null Stephan Szabo [EMAIL PROTECTED] On Fri, 15 Dec 2000 [EMAIL PROTECTED] wrote: > Grzegorz Mucha ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > Outer joins aren't working with views > > Long Description > It seems outer joins are not working at all(they work as inner joins so far). > For example, see below: > (the result is identical for inner and outer join) - two rows fetched from db(as I >recall, there should be one more row having t1.id=3) > > Sample Code > create table t1(id serial primary key); > create table t2(id2 serial primary key, id int); > insert into t1 values (1); > insert into t1 values (2); > insert into t1 values (3); > insert into t2 (id) values(1); > insert into t2 (id) values(2); > select t1.*, t2.* from t1 natural left outer join t2; > > No file was uploaded with this report >
Re: [BUGS] CONSTRAINT problems
This a known problem which is caused by subselects in check constraints. Current sources seem to give an error message at create time: ERROR: Cannot use subselect in CHECK clause In general subselects in check constraints also may constrain tables that the subselect mentions. In your case this wouldn't probably be a problem, but it's difficult to tell the cases apart. You can probably do this with a before insert/update trigger in pl/pgsql at the current time though. > I've got a test table in 7.0.3 where I'm implementing a directory > type structure in a RCS type system. I have a check to ensure that > there are no duplicate filenames within for a specific directory > given the revisionid of 0 (means it's the current revision rather > than a historical one). > > CREATE TABLE test ( > id int4 primary key, > name VARCHAR(20), > revision int4 default 0, > directoryid int4, > CONSTRAINT dupename CHECK (NOT EXISTS (SELECT name FROM test as o > WHERE o.name!=name AND revision=0)) > ); > CREATE > insert into test values (1,'test','0',NULL); > ERROR: ExecEvalExpr: unknown expression type 108
Re: [BUGS] Bug in SQL functions that use a NULL parameter directly
On Sun, 14 Jan 2001, Michael Richards wrote: > Hi. > > I'm using 7.0.3 and I've found a bug: > > create table test(value int4); > create function testfunc(int4) > RETURNS bool AS > 'SELECT count(*)>0 AS RESULT FROM test where value= $1' > language 'SQL'; > > So I want this function to return true when it finds the specified > value in the table. It does not work when you have a null in the > table and call it with a null. This is actually probably correct. NULL=NULL is not true but unknown which will not satisfy the where clause. The reason such a query does something different from the psql prompt is that the parse is looking for =NULL to turn it into IS NULL due to broken MS Acess statements. In this case it doesn't know to turn it into an ISNULL and so instead does a comparison which will never be true according to spec.
Re: [BUGS] Bug in SQL functions that use a NULL parameter directly
On Sun, 14 Jan 2001, Michael Richards wrote: > I do not understand how this can possibly be correct unless NULL is > not permitted in a function. > > In one case, I've got: > WHERE value= $1 > Which is called with NULL and therefore should be: > WHERE value= NULL > This fails. Right, but value=NULL is *NOT* true when value is NULL. That's what the spec says. value=NULL where value is NULL is unknown not true, therefore WHERE value=$1 ($1 being NULL) is never going to be true. > The other case which is logically equivalent I've got: > WHERE value= $1 OR ($1=NULL AND value=NULL) > This passes. > > So I get a true and a false from the same logical statement. I am not > using anything to do with MS Access, so I do not see how it may be > involved with this problem. Because of Access's brokenness, the parser or some other layer of the code "fixes" explicit =NULL (ie, in the actually query string) into IS NULL which is the correct way to check for nulls. The statement should be (and would get converted to): WHERE value = $1 OR ( $1 IS NULL AND value IS NULL) ISNULL returns TRUE if its argument is null and FALSE otherwise, so you have UNKNOWN OR (TRUE AND TRUE) which is TRUE, as opposed to simply UNKNOWN. Because your original query was = $1, it doesn't do the mangling of the SQL to change into IS NULL when $1 is NULL. The fact that we do that conversion at all actually breaks spec a little bit but we have little choice with broken clients.
Re: [BUGS] cannot index functions with multiple arguments
IIRC, index functions can take multiple arguments, it's just that they cannot take constants. I think the values must all be columns of the tables. So for example, this should work (seems to at least create on 7.1beta3): create table aaa (a int, b int, c text); create index blah on aaa(substr(c,a,b)); On Wed, 31 Jan 2001 [EMAIL PROTECTED] wrote: > Scott Marlowe ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > cannot index functions with multiple arguments > > Long Description > Postgresql version: 7.1beta2 > OS: RedHat Linux 7.0 > > Postgresql allows indexing of functions applied to columns, like so: > > create index yada on table (lower(column)); > > but this fails on functions like substr that have multiple arguments. > > create index yada on table (substr(column,1,4)); > > fails like so: > > ERROR: parser: parse error at or near "1" > > Sample Code > Exact code as I typed it in, with response: > > smarlowe=# create index exten_extension_idx on exten (substr(extension,1,4)); > ERROR: parser: parse error at or near "1" > > I hope this isn't a known bug and I'm reporting it again. > > No file was uploaded with this report >
Re: [BUGS] Referencial integerity problem
Under 7.0 you needed update permission on the referenced table to grab the necessary locks. Under 7.1 you won't need this anymore. On Thu, 8 Feb 2001, Mike Howard wrote: > Briefly, I create two tables, one having a column which references the > other and which implements cascade deletes and updates. I create a user > who has modify access on one table, but only select on the referenced > table. This user is not allowed to insert a record into the referencing > table - the error message refers to the referenced table.
Re: [BUGS] alter table rename and ruminations on referential integrity
> It appears that the triggers were never updated to now refer to 'old' > instead of to current_usage when it was renamed, but were still > associated with 'old' for purposes of dropping them. (i presume this > is because the triggers were attached to 'old' by oid, which didnt > change when it was renamed, but the triggers themselves referred to > current_usage by name, and this wasn't altered?) IF this is the case, > i'll feel better, as just dropping the 'old' tables will fix the > problem, otherwise i'm worried about my database state as i've applied > several upgrades by renaming older versions of tables out of the way, > and creating new ones (when the change required wasn't within the > capabilities of alter table), and copying the data from the old table > to the new table, then dropping the old table. You basically have this right. The trigger uses the tablename to make a SPI query to the table while the drop test uses the value in pg_trigger.tgconstrrelid to do the other drop. Eventually we'll be using the OID in the trigger as well, but there are a few side steps to that. > In these cases, no other tables refer referentially to the table in > question, except by the triggers the table in question created itself. > i'm not sure how i'll manage if I ever need to alter a non-leaf table. You can probably get away with dropping the table you've renamed and using alter table to re-add the constraint to tables that refer to the new table of that name. > Is there a way to temporarily disable triggers for a session, other > than deleting them, then putting them back when done? I haven't tried, but you may be able to twiddle pg_trigger.tgenabled.
Re: [BUGS] regcomp problems
On Wed, 21 Mar 2001, Romolo Manfredini wrote: > Dear postgresql developers, > I have found a small annoying bug in the SQL parser, > > executing the following query; > > select * from table where field ~* '*'; > > or > > select * from table where field ~* '+'; > > generate the following error: > > regcomp failed with error repetition-operator operand invalid > > the postgres I'm using is 7.0.3 compiled on linux 2.2.18 You're doing a regexp, both * and + have special meanings in a regexp, you'll probably want to escape them (unfortunately I think you need two backslashes to do it) ---(end of broadcast)--- TIP 3: 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] Deleting Objects...
On Sun, 25 Mar 2001, Rolando Lora wrote:
> Hi there...
>
> I'm not sure if this is a "bug" or not but I couldn't
> find how to deal with it.
>
> For instance if we create a table using "Object ID"
> for storing large objects:
>
> > create table picture(id serial, pic_oid oid);
>
> and then we add some data:
>
> > insert into picture
> (pic_oid) values (lo_import('/tmp/image.jpg'));
>
> We'll insert into the table an "Object Id" pointing to
> an external file "xin...OID".
>
> But when we execute:
>
> > delete from picture;
>
> The data contained in the table gets deleted but the
> real object "xin...OID" remains untouched when (I
> Think) it should be deleted too. Is this a bug?
>
> I'm developing an application where I need to upload a
> lot of pictures but when I delete some of them I don't
> want files floating around that used to be pictures.
>
> Is there anyway I can deal with it?
Generally speaking I think right now you need to put
a trigger on the table to remove the large objects when
the row is deleted.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug about 'Foreign Key'
This should be fixed for 7.1, it now checks that the
referencing columns exist on the fk table and that the
referenced columns are part of a unique index
[our unique/pkey] on the referenced table.
On Tue, 27 Mar 2001, Cid R Andrade wrote:
> PostgreSQL Developers,
>
> I have a PostgreSQL 7.0.2 running at Conectiva Linux, with kernel 2.2.14, with 56 Mb
>of data.
>
> I'd created a table:
> Create Table Andamento (
> Etiqueta integer not null,
> CodTecnico integer not null,
> DtInicio timestamp not null,
> DtLiberacao timestamp null,
> Primary Key (Etiqueta),
> Foreign Key (Etiquetas) References Etiquetas (Etiqueta) On Delete Cascade)
> I wrong a field name in Foreign Key declaration,
> and PostgreSQL created it! Before this, I always named triggers and
> constraints, but, at this time, I'd tried this way. Well, look the
> trigger at table Etiquetas: CREATE CONSTRAINT TRIGGER ""
> AFTER DELETE ON "etiquetas" NOT DEFERRABLE INITIALLY IMMEDIATE FOR
> EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('',
> 'andamento', 'etiquetas', 'UNSPECIFIED', 'etiquetas', 'etiqueta');
>
> It says exists a field named etiquetas at table Andamento.
> I'd droped table andamento (Drop Table Andamento) and the trigger referencing it
>didn't droped.
>
> I solved it with pg_dump.
>
---(end of broadcast)---
TIP 3: 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] error creating table that worked under 7.0.3
> I am not sure whether this is a bug or a tightening-up of a previous
> loose SQL definition.
> Previously, I was able to define constraints that refer to the same
> table within the table itself,
> like so:
>
> CREATE SEQUENCE incidents_id_seq;
> GRANT ALL ON incidents_id_seq TO its;
> CREATE TABLE incidents (
>-- generated sequence ID for this incident
>id INTEGER DEFAULT nextval('incidents_id_seq'),
>
>-- if the resolution is to determine that this
>-- bug is a duplicate, this will contain the
>-- duplicate ID
>duplicate_iid INTEGER NULL,
>
> -- if an incident is declared as a duplicate of another
> -- incident and that incident is deleted, cascade to
> -- delete this one too
> CONSTRAINT duplicate_iid_exists
> FOREIGN KEY(duplicate_iid) REFERENCES incidents(id)
> ON DELETE CASCADE
> );
>
> In 7.1rc2, this same DDL results in this error:
>
> ERROR: UNIQUE constraint matching given keys for referenced table
> "incidents" not found
You'll need something like:
id INTEGER DEFAULT nextval('incidents_id_seq') UNIQUE,
(or PRIMARY KEY) for the definition of id for the above to be
legal.
SQL technically only allows references to the cols belonging to a
UNIQUE or PRIMARY KEY constraint (and all the cols of
said constraint) but we didn't check that previously, mostly because
we don't prevent you from dropping the constraint implicitly (by
deleting the unique index) later out from under it, but the create
time check fixes some loopholes in any case.
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 3: 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] Error on reference to inherited primary key
On Fri, 13 Apr 2001 [EMAIL PROTECTED] wrote: > Creating tables this way: > >A has a primary key >B inherits A >C references B > > results in an error message on the CREATE TABLE for C (ERROR: PRIMARY KEY for >referenced table "b" not found). > > To reproduce: > > create table A > ( > id integer primary key > ); > > create table B > ( > dummy integer > ) inherits (A); > > create table C > ( > ref integer references B > ); > > psql:repro.sql:14: ERROR: PRIMARY KEY for referenced table "b" not found > > AFAIK the SQL code is correct. B should inherit the primary key from A, so C should >be able to reference B. Primary keys/Unique constraints do not currently inherit (they should, but there are some questions about how they should). If you want id to have the properties of a primary key on B, you need to make a unique constraint on B, I believe the NOT NULL is already inherited. > The workaround appears to be explicitly naming the primary key column: > create table C > ( > ref integer references B(id) > ); This will not work for 7.1 since it checks for unique constraints (see above) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Terrible perfomance during nested "... where x in (select ...)" operator
Known issue, see FAQ item 4.23. Short form, use exists rather than in. On Tue, 8 May 2001, [KOI8-R] þÅÒÅÐÁÎÏ× ìÅÏÎÉÄ wrote: > Leonid ([EMAIL PROTECTED]) reports a bug with a severity of 2(?) > > Short Description > Terrible perfomance during nested "... where x in (select ...)" operator > > Long Description > PostgreSQL 7.1, FreeBSD 4.2-STABLE > > Analyzing the reasons for terrible perfomance of my query I've found > a very strange thing. Here is sublimation. > > Queries like > select distinct i from t1 where i in > (select distinct i from t2 where j in >(select distinct j from t3)); > is much-much slower than > select distinct t1.i from t1,t2,t3 where t1.i=t2.i and t2.j=t3.j; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] table named primary
On Thu, 19 Apr 2001, Joseph Crawley wrote: > I'm not sure if this is a bug or feature but in ver7.1 on linux you > can't have a column name "primary". Probably because it's a reserved word as part of PRIMARY KEY. Actually if you double quote the name everywhere you use it, it'll probably work, but that's probably a bad idea in general. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Foreign key constraints on inherited columns, 2 errors.
On Wed, 16 May 2001 [EMAIL PROTECTED] wrote: > Stephen Deasey ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > Foreign key constraints on inherited columns, 2 errors. > > Long Description > Using the Postgres 7.1.1 RPMs I typed the following into psql: > > =# create table foo (foo integer); > CREATE > =# create table bar (bar integer) inherits(foo); > CREATE > =# create table baz (baz integer references bar(foo)); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > ERROR: UNIQUE constraint matching given keys for referenced table "bar" not found > > > I think this is incorrect, and it is not the behaviour I was seeing > with version 7.0.3 where it worked as expected. No, actually it didn't really. If bar had non-unique values the references constraint would do fairly random bad things. SQL requires the unique constraint on the columns, and so do we now. Just add a UNIQUE(foo) to bar's definition and it should work. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] orphaned trigger
On Wed, 16 May 2001, Jonathan Ellis wrote:
> This is in 7.1.1:
>
> I was playing around with a table called user_comments and then dropped it.
> One of its columns referenced the user_id column of another table, users.
> Now whenever I try to update users, I get results like this:
> bf2-new=# update users set last_visit=sysdate() where user_id=4; ERROR:
> Relation 'user_comments' does not exist
>
> Somehow a constraint trigger that should have been dropped wasn't.
>
> I looked at the dump file and this is the only reference to user_comments:
>
> CREATE CONSTRAINT TRIGGER "" AFTER UPDATE ON "users" NOT DEFERRABLE
> INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"
> ('', 'user_comments', 'users', 'UNSPECIFIED', 'user_id',
> 'user_id');
>
> but I am not out of the woods here because it won't let me drop it:
>
> bf2-new=# drop trigger RI_ConstraintTrigger_44349 on users; ERROR:
> DropTrigger: there is no trigger ri_constrainttrigger_44349 on relation
> users
>
> It's in pg_trigger all right -- and with tgrelid = (select relfilenode from
> pg_class where relname = 'users') -- but it won't drop. Would manually
> removing it from pg_trigger cause Bad Things to happen?
Yes, and you'll need to double quote the trigger name.
try
drop trigger "RI_ConstraintTrigger_44349" on users;
How did you drop the table? Did you reload a dump file (like the one that
referenced it)?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] orphaned trigger
On Wed, 16 May 2001, Jonathan Ellis wrote: > > > Would manually > > > removing it from pg_trigger cause Bad Things to happen? > > > > Yes, and you'll need to double quote the trigger name. > > try > > drop trigger "RI_ConstraintTrigger_44349" on users; > > That worked. Why was that? It's a mixed case thing. The name of the constraint is actually RI_ConstraintTrigger_44349 (with that casing). When you specify it without the quotes, postgres lowercases it to ri_constrainttrigger_44349 and fails to find it. > > How did you drop the table? Did you reload a dump file (like the one that > > referenced it)? > > I'm afraid I don't understand the question. I dropped it with drop table. > Although thinking back on it, the drop may have been on 7.0.2. Can't > remember whether that was before or after upgrading... Wait, okay, I think I know what happened. There was a problem with 7.0.? dumps not having the information about the "other" table in the references constraint written out so when you dropped the table it didn't realize it needed to drop the trigger. You may want to drop the constraint triggers and recreate them with alter table add constraint. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] references fail over rows inserted via inherited tables
On Sun, 20 May 2001 [EMAIL PROTECTED] wrote: > Diego Saravia ([EMAIL PROTECTED]) reports a bug with a severity of 4 > The lower the number the more severe it is. > > Short Description > references fail over rows inserted via inherited tables > > Long Description > When you insert a row via an inherited table, with 7.1.1, you can see in the parent >table. > > But when you try to insert a reference to that value you can't. Yes. Right now the constraint only references the rows actually existing in the parent table not the hierarchy. (See archives of -bugs and -general for alot of message about this). > > create table trabajo2( > titulo text, > propietario integer references usuario ); > > > /* > ERROR: PRIMARY KEY for referenced table "usuario" not found > > I think that this is also not Ok > > */ This is because primary keys do not currently inherit to child tables, so there really isn't a primary key on usuario. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] REFERENCES fails on derived classes
On Sat, 2 Jun 2001, J. Michael Caine wrote: > the following works: > > create table t1 (id serial primary key); > create table t2 (id serial primary key); > create table t12 ( > t1_id int references t1(id) on delete cascade, > t2_id int references t2(id) on delete cascade > ); > > but the following does not: > create table t1d () inherits(t1); > create table t1d2 ( > t1d_id int references t1d(id) on delete cascade, > t2_id int references t2(id) on delete cascade > ); > > i'm told: > ERROR: UNIQUE constraint matching given keys for referenced table t1d not > found > > i'm working in postgresql 7.1 (i686-pc-linux-gnu) > > hope this is a bug (and i'm not wasting your time) and easily verifiable, Sort of. The unique constraint does not get inherited by t1d right now (nor do fk constraints inherit) so there isn't a unique constraint on t1d(id). Add a unique constraint to t1d(id) and it should work. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] password check
On Mon, 4 Jun 2001, Manager, RIKOP I.S.P. wrote: > Dear Sir or Madam. > We'we noticed that command > psql -U user database > don't check password. However databese was created next commad > createdb -U user -W databese What does your pg_hba.conf say? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Help!
On Thu, 31 May 2001, Letitia Hickman wrote: > Getting a "Warning: PostgreSQL query failed: ERROR: Cannot insert a > duplicate key into unique index > auditlog_pkey in /htdocs/ecorp-real-estate.com/intranet on line 167" > > Continue to get also > > :Warning: PostgreSQL query failed: ERROR: parser: parse error at or near > "," in /htdocs/ecorp-real-estate.com/Include/propertyPost.inc on line > 18" > > Can't seem to solve. That's not alot of information. Basic version, schema and query details are going to be needed. The first is a reasonable error if someone tried to insert the same value into the pkey field of auditlog that already existed. The second is a parse error, probably a syntactic problem. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [BUGS] Problem with sql
I do not see this (using fairly random table creations since you didn't provide the schema) in 7.2devel. I get 7 and 7 at the end. Which 7.1 are you using? Have you tried upgrading to 7.1.2? In any case, a script to create the tables and views and populate them with the appropriate data would be helpful here since it could be dependant on the actual table structure. On Thu, 31 May 2001, shashi ahuja wrote: > i have four same tables in postgres and oracle.I'm > using the postgres 7.1 version. > listed below > > GROUPSMS=# select * from group_smu_trans ; > sub_id | con_phone | > message | > >+--+--+- > 2 | 919810058237 | hi checking >| 2001-05-01 > 2 | 919810299102 | hi checking >| 2001-05-01 > 2 | 919810058237 | hi checking >| 2001-05-01 > 2 | 919810299102 | hi checking >| 2001-05-01 > 2 | 919810058237 | hi checking >| 2001-05-01 > 2 | 919810299102 | hi checking >| 2001-05-01 > 2 | 9810125422 | Kindly Check . > | > 2001-05-01 > (7 rows) > > GROUPSMS=# select * from subscriber_mast ; > sub_id | sub_name | sub_username | sub_pwd | > sub_phone |sub_add | sub_type | plan > >+---+--+---+---++--+-- > 2 | veeren| veeren | veeren| > 8976548 | kljhjkhdslkjhf | o| c > 10 | textarea1 | textarea1| textarea1 | > textarea1 | textarea1 | o| c > (2 rows) > > GROUPSMS=# select * from sub_group_contact_lnk; > sub_id | group_id | contact_id > +--+ > 2 | 18 | > 2 | 18 | 27 > 2 | 21 | > 2 | 21 | 33 > 2 | 21 | 37 > (5 rows) > > GROUPSMS=# select * from pre_sub_bal ; > sub_id | sub_total | sub_current | msg_rate > +---+-+-- > 3 | 350 | 200 | 125 > 4 | 250 | 200 | 75 > 7 | 500 | 500 | 75 > 8 | 250 | 250 | 75 > 1 | 250 | 125 | 125 > 2 | 250 | 117.25 | 125 > (6 rows) > > create view mis_group_smu_trans > as > select sub_id,count(message) as > tot_message,count(con_phone) as con_phone from > group_smu_trans > group by sub_id; > GROUPSMS=# select * from mis_group_smu_trans ; > sub_id | tot_message | con_phone > +-+--- > 2 | 7 | 7 > (1 row) > > create view mis_sub_group_contact > as > select sub_id,count(distinct group_id) as > group_id,count(distinct contact_id) as contact_id from > sub_group_contact_lnk > group by sub_id; > GROUPSMS=# select * from mis_sub_group_contact; > sub_id | group_id | contact_id > +--+ > 2 |2 | 3 > (1 row) > > create view test1 as > select > a.sub_id,a.sub_name,c.tot_message,c.con_phone,d.sub_total,d.sub_current > from > subscriber_mast a, > mis_group_smu_trans c, > pre_sub_bal d > where > a.sub_id=c.sub_id and > a.sub_id=d.sub_id; > GROUPSMS=# select * from test1; > sub_id | sub_name | tot_message | con_phone | > sub_total | sub_current > +--+-+---+---+- > 2 | veeren | 7 | 7 | > 250 | 117.25 > (1 row) > > create view test2 as > select > a.sub_id,b.group_id,b.contact_id > from > subscriber_mast a, > mis_sub_group_contact b > where > a.sub_id=b.sub_id; > > GROUPSMS=# select * from test2; > sub_id | group_id | contact_id > +--+ > 2 |2 | 3 > (1 row) > > > select a.*,b.* from test1 a ,test2 b > where b.sub_id=a.sub_id; > > GROUPSMS-# where b.sub_id=a.sub_id; > sub_id | sub_name | tot_message | con_phone | > sub_total | sub_current | sub_id | group_id | contac > >+--+-+---+---+-++--+ > 2 | veeren | 35 |35 | > 250 | 117.25 | 2 |2 | 3 > (1 row) > > > > > > >Now wht happen is in my final query of test1 > and test2, "total_message" and "con_phone" both > the columns output is changed by 5 times which is a > random value. > > >
Re: [BUGS] furiously yours
That's mostly because your c program is wrong. If you reorder the strings
you'll see that it's not sorting at all. It's just giving them back
in the order you gave them.
You should not compare the output from strcmp to -1. It's <0, 0, >0 not
-1, 0, 1. When I run the below program and add a printf of the
strcmp values, I get values like 40 and -40. Also, you'd probably want
to be using strcoll rather than strcmp to make the comparison valid.
[As a side note, I believe this means that the results from varstr_cmp
in varlena.c are not guaranteed to be -1/0/1 as the comment purports
if strncmp doesn't return -1/+1.]
Try the following program and switch which setlocale is enabled:
#include
void main () {
char tparr[10][500];
char tpstring[500];
int i,j;
/* setlocale(LC_ALL, "en_US"); */
setlocale(LC_ALL, "C");
sprintf(tparr[0],"BOU ASSAF");
sprintf(tparr[1],"BOU ZAHRA");
sprintf(tparr[2],"BOUHAIDAR");
for(i=0;i<3;i++) {
for (j=0;j<3;j++) {
if (strcoll(tparr[i],tparr[j])<0) {
sprintf(tpstring,"%s",tparr[i]);
sprintf(tparr[i],"%s",tparr[j]);
sprintf(tparr[j],"%s",tpstring);
}
}
}
for (i=0;i<3;i++) {
printf("\n---%s",tparr[i]);
}
printf("\n");
}
With C you should get:
---BOU ASSAF
---BOU ZAHRA
---BOUHAIDAR
With en_US you should get:
---BOU ASSAF
---BOUHAIDAR
---BOU ZAHRA
On Sun, 10 Jun 2001, Rony Khoury wrote:
>
> Hello again,
>
> If I understood right Postgresql depends on C language to do the sorting,
> and C language depends on the settings of redhat for the sorting task.
> Before going to RedHat with that I took the liberty to do the following test
> and I would like to take your opinion about it first.
>
> I installed a whole new redhat 7.1 version at home, with it came postgresql
> 7.0.3, I then downloaded the rpms for postgresql 7.1.1 from the internet and
> upgraded to it. After doing all the proper installation procedure, I tried
> the sorting on the new system and got the same results as at work.
>
> Now I took the liberety to write a small program in C-language to see how
> the sorting works and surprisingly enough I got the results desired that I
> used to get on the old version. (ie the space is interpreted as < A).
>
> Following is the code I wrote and the results I got. I belive this requires
> your comment before going to redhat with that, I still think that there
> might be some parameters missing somewhere to put things back in order.
>
> Lamer I checked the parameters on my system and did not find LC_ALL nor
> LC_COLL, but I found LANG=en_US. I do not know what these do, so your
> guidance is appretiated in this respect if you think this is related to the
> problem.
>
> The C-Language program is:
>
> main () {
> char tparr[10][500];
> char tpstring[500];
> int i,j;
>
> sprintf(tparr[0],"BOU ASSAF");
> sprintf(tparr[1],"BOUHAIDAR");
> sprintf(tparr[2],"BOU ZAHRA");
>
> for(i=0;i<3;i++) {
> for (j=0;j<3;j++) {
> if (strcmp(tparr[i],tparr[j]) == -1) {
> sprintf(tpstring,"%s",tparr[i]);
> sprintf(tparr[i],"%s",tparr[j]);
> sprintf(tparr[j],"%s",tpstring);
> }
> }
> }
>
> for (i=0;i<3;i++) {
> printf("\n---%s",tparr[i]);
> }
> printf("\n");
> }
>
>
> and the result is
>
> ---BOU ASSAF
> ---BOU ZAHRA
> ---BOUHAIDAR
>
> while the postgresql continues to interpret this info as
>
> ---BOU ASSAF
> ---BOUHAIDAR
> ---BOU ZAHRA
>
>
> not that these results I'm getting on the same system and is all a new
> system from scratch thus can not be dependant on any previous versions.
>
> Thanks,
> Rony.
> _
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>
> ---(end of broadcast)---
> TIP 3: 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
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: Re(2): [BUGS] REFERENCES fails on derived classes
On Mon, 11 Jun 2001, Michael Caine wrote: > a month i was told that my possible bug submission was "sort of" a bug. > the report and reply are below. > > now i write a follow up because i don't know how to establish a workroom. > specifically, i know know that a unique constraint must be added to an > inherited "id" field, even if it's a primary key in the base table. i can > do this by 'CREATE INDEX', but this creates a completely new constraint. > i want to share the unique 'serial' behavior between the base table and > derived table, so that en entry in either table is unique between the > tables. that way, when i do a 'SELECT baseTable*' kind of command, i can > differentiate between two records. is there a way to do this, or could > this be considered a valid bug of some sort? Unfortunately there's no way within the current constraints to do that kind of unique constraint, although you should get actually unique numbers out of the serial -- and those will span the two tables since it uses the same sequence, you can't guarantee that explicitly placed values will be unique. This is probably a bug, but inheritance needs alot of work in general. You might be able fake it with a insert/update trigger in plpgsql that makes sure that there are no matching rows. It wouldn't exactly be the same thing as a unique constraint, but it'd probably be close enough for most use. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] where clause bug
On Fri, 8 Jun 2001, kyle wrote: > When specifying criteria in a where claue such as > user_id = 'value' if a non-numeric is entered the > query returns back all rows. > user_id = user_id brings back every user_id instead of > error because user_id is a numeric field and it > shouldnt be able to accept chars. What version are you running? Can you give a test case of the first case (user_id='value' for non-numeric value) with schema and sample data? When I try something like that on current sources I get "Bad numeric input format 'value'" for a numeric column. The second looks okay (user_id=user_id) since the value of the user_id column is going to be equal to itself for any non NULL value in user_id. I don't know why you'd expect that one to error. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Information regarding foriegn key constraint.
On Fri, 8 Jun 2001, meeta bhate wrote: > Dear Sir, > I am a Graduate student at the University of Toledo.My Master's project > involves creating a database in PostgreSQL version 6.5.3.I was wondering > if I can use SERIAL key along with a PRIMARY key as follows: > > create table ohiouniv (uid serial primary key, > uname varchar(45) not null, > ucity varchar(25) not null); > > create table departments (deptid serial primary key, >deptshrtn varchar(10) not null, >deptfulln varchar(45) not null, >deptphone int8 unique not null check(deptphone>0)); > > When I tried giving the following for linking the above 2 tables: > > create table univdept (uid integer references ohiouniv, > deptid integer references departments); > > The error was: > create table/foreign key clause ignored; not yet implemented > > Does this mean we cannot use the foreign key constraint in this version of > PostgreSQL, and if so, what is the alternative for the above problem.Please > let me know about this at your earliest convinience. FK constraints were added in 7.0, various fixes in 7.1 (with more forthcoming). Your best bet would be to upgrade if possible. There was a contrib module in 6.5 that did something basically fk (it didn't use the references syntax and was installed manually as triggers). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [BUGS] Strange CREATE VIEW behavior??
On Thu, 14 Jun 2001 [EMAIL PROTECTED] wrote:
> Basil A. Evseenko ([EMAIL PROTECTED]) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> Strange CREATE VIEW behavior??
>
> Long Description
> createdb test
> psql test
>
> test=# create table zz(ss int, ff varchar);
> test=# create view ff2 as select ff as user, ss as num from zz;
> test=# create view ff3 as select ff as user1, ss as num from zz;
> test=# \d ff2
>View "ff2"
> Attribute | Type| Modifier
> ---+---+--
> user | character varying |
> num | integer |
> View definition: SELECT zz.ff AS "user", zz.ss AS num FROM zz;
>
> test=# \d ff3
> View "ff3"
> Attribute | Type| Modifier
> ---+---+--
> user1 | character varying |
> num | integer |
> View definition: SELECT zz.ff AS user1, zz.ss AS num FROM zz;
>
> Why in the first case column has name "user" (with '"') ?
Probably because user is sometimes reserved. For example:
create table foo(user int); -- fails
create table foo("user" int); -- succeeds
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] SELECT updatability clause defective.
On Wed, 9 May 2001, Peter Eisentraut wrote:
> > Jerome O\'Neil ([EMAIL PROTECTED]) reports a bug with a severity of 2
>
> > SELECT updatability clause defective.
>
> Nope.
>
> > The syntax for select updatability clause is non-standard.
>
> That's not a bug.
>
> > While SQL-92 BNF describes it as
> >
> > SELECT ...
> > [FOR UPDATE [OF ]]
>
> Chapter and verse?
I see it as part of declare cursor (13.1)
::=
FOR { READ ONLY | UPDATE [ OF ] }
However, I think the updatability clause means something
different than our for update. If I'm reading it write, I think
it tells you whether or not you may do positioned updates on
the cursor.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Referential integrity problem
On Mon, 18 Jun 2001 [EMAIL PROTECTED] wrote: > Denis Sbragion ([EMAIL PROTECTED]) reports a bug with a severity of 3 > The lower the number the more severe it is. > > Short Description > Referential integrity problem > > Long Description It looks like performing operation in two steps on > tables with multiple deferred referential integrity constraints > between them is not allowed. Not sure this is a bug, may be it is > required by the standards, but sure it is rater counterintuitive (may > be, of course, I'm completely wrong). Most of the times this is just > annoying, sometimes it doesn't let you do updates, i.e. whenever you > cannot perform the update in a single step (I found at least one > case). Tested both on 7.0.3 and 7.1.2, with same results. > > P.S. Great job guys. 7.0.3 was very good, but 7.1.2 is simply wonderful. Yep. AFAICT it's currently broken for such cases. I've been working (slowly :() on fixing it so it doesn't try the checks on rows that are no longer valid (such as the intermediate states of multiple updates). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Foreign keys don't work with inherited tables
On Sat, 23 Jun 2001, Micah Yoder wrote: > Using PostgreSQL 7.1.2 compiled with Red Hat 7.1's gcc 2.96. > > I have a database that uses inherited tables. The top level table of the > hierarchy has a serial column, which is the primary key. Everything inserted > into any of its decendents and each shares the primary key and sequence of > the top table. That works fine. > > Another table (outside the hierarchy) uses a foreign key to the top level > primary key. Inserting into this table works when the foreign key references > an item that is in the TOP level class of the hierarchy. But when it > references a subclass, I get a "referential integrity violation". > > Attached is a file with everything you should need to see what's up. > > Please let me know if you need more info, and if there are any workarounds. > I guess I could not use a foreign key there but that would kind of suck ... Yes. This doesn't work (see long discussions about this on -general, -hackers and -bugs in the archives) and probably won't work until inheritance gets a serious looking at. The only workaround we've seen is having a separate id table with each of the inherited tables referencing into it for their ids and other tables reference the id table rather than the inheritance tree. Side Note: Unless you define a primary key on those inherited tables they do not share the primary key of the parent, although they do share the default (for the sequence). In addition, even if you do put a primary key on each of them, they're separate keys, so you can end up with duplicated values in different trees if you ever insert explicit values into tables. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [BUGS] Referential Integrity corrupted sometimes by Rules
On Fri, 6 Jul 2001 [EMAIL PROTECTED] wrote: > José María Fernández González ([EMAIL PROTECTED]) reports a bug with a severity >of 1 > The lower the number the more severe it is. > > Short Description > Referential Integrity corrupted sometimes by Rules > > Long Description If you define an empty rule with a condition over a > table which references to another table with ON DELETE CASCADE (or ON > UPDATE CASCADE), referential integrity is violated letting entries in > the referer table. Even more, referential integrity doesn't work even > if the rule is erased. I was able to reproduce this bug until I got > the next message on the psql console: > > NOTICE: InvalidateSharedInvalid: cache state reset > NOTICE: InvalidateSharedInvalid: cache state reset > > And then it just worked well again. The reason removing the rule doesn't work is because the plan gets cached on first use (you'd see similar behavior in a plpgsql function). The question for working with this is, what is the correct behavior in such a case if you've explicitly turned off deletes for a subset of the table. I'd guess it should error with a deleted row still referenced, but I'm not sure. ---(end of broadcast)--- TIP 3: 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] PL/pgSQL triggers with parameters don't work
On Wed, 11 Jul 2001 [EMAIL PROTECTED] wrote: > José María Fernández González ([EMAIL PROTECTED]) reports a bug > with a severity of 2 > The lower the number the more severe it is. > > Short Description > PL/pgSQL triggers with parameters don't work > > Long Description > The report is for PostgreSQL 7.1.2. When you create a trigger > which calls a function with parameters, the system links against the > function with the same name and no parameter, and if it doesn't exist > it tells "function doesn't exist". Tests have been made with > PL/pgSQL-encoded functions. Trigger functions take their arguments in a different fashion. They should be defined as taking no regular arguments and returning opaque and it's something like TG_ARGS in plpgsql that the create trigger time arguments are passed in. I believe this is described in the documentation. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] No subselects in constraint (bug?)
On Fri, 13 Jul 2001, Alexey V. Neyman wrote: > Hello there! > > [Please Cc: me in followups.] > > I tried the following: > > CREATE TABLE a ( > int4 id > ); > CREATE TABLE b ( > int4 id > CHECK (id = ANY(SELECT a.id FROM a)) > ); > > Tables are created ok, checking with '\d table' confirms it. But when I > try to insert into table b, e.g.: > INSERT INTO b (id) > VALUES (0); > I get: > ERROR: ExecEvalExpr: unknown expression type 108 > Of course, the tuple is not inserted. > > As quick dig of code showed, type 108 is T_SubLink which is created for > ANY() subselect, and ExecEvalExpr() function does not handle this type of > node. Is it intentional or a bug? It's unimplemented, and really should fail at create time (I'm not sure if it does in 7.1). IIRC, it's only required at FULL SQL92 level (intermediate level has a no subqueries limitation). The reason is that the constraint you are making as part of b also constrains table a and it's not entirely trivial to support complicated subquery constraints within the current system. As a workaround for now, you'll probably have to use triggers on a and b to do the check. (before insert trigger on b and a delete/update trigger on a). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Two foreign keys in one table both referencing same recordin primary table gives error on update of primary table
Yes, it's because it sees the intermediate state (6,1) when it shouldn't (it processes each foreign key separately and does the cascade update which places it in an "invalid" state since the 1 key no longer exists). I believe the fixes I've been working on for deferred constraints will make this case work as well. On Wed, 18 Jul 2001 [EMAIL PROTECTED] wrote: > Bob Soeters ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description Two foreign keys in one table both referencing same > record in primary table gives error on update of primary table ---(end of broadcast)--- TIP 3: 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] ERROR: CreateTrigger: function check_primary_key() doesnot exist
On Thu, 19 Jul 2001 [EMAIL PROTECTED] wrote: > Young ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > ERROR: CreateTrigger: function check_primary_key() does not exist > > Long Description > I am using 7.1.2 > > I always got such an error when creating a trigger: > ERROR: CreateTrigger: function check_primary_key() does not exist > ERROR: CreateTrigger: function check_foreign_key() does not exist What specifically were you trying to do? If you're looking to enforce referential integrity, use the REFERENCES constraint (should be some info in the docs about the specifics) ---(end of broadcast)--- TIP 3: 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] real or float4 '=' operator is broken in 7.1.2
More information to add to this bug.
I get the same behavior as the original poster, but the following on these
queries:
sszabo=> select myfloat-29.92 from "test_f4";
?column?
--
7.62939436071974e-08
7.62939436071974e-08
(2 rows)
sszabo=> select myfloat-'29.92' from "test_f4";
?column?
--
0
0
(2 rows)
This is probably due to the float constant being made
a float8 since I also see the following:
sszabo=> select 29.92::float4-29.92::float8;
?column?
--
7.62939436071974e-08
(1 row)
On Tue, 24 Jul 2001 [EMAIL PROTECTED] wrote:
> CREATE TABLE "test_r" ("key" serial, "myfloat" real);
> CREATE TABLE "test_f" ("key" serial, "myfloat" float8);
> CREATE TABLE "test_f4" ("key" serial, "myfloat" float4);
> INSERT INTO "test_r" ("myfloat") values ('29.92');
> INSERT INTO "test_r" ("myfloat") values (29.92);
> INSERT INTO "test_f" ("myfloat") values ('29.92');
> INSERT INTO "test_f" ("myfloat") values (29.92);
> INSERT INTO "test_f4" ("myfloat") values ('29.92');
> INSERT INTO "test_f4" ("myfloat") values (29.92);
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] 'order by' and 'desc' not working in subquery using 'notin'
On Tue, 31 Jul 2001 [EMAIL PROTECTED] wrote: > Rahul Gade ([EMAIL PROTECTED]) reports a bug with a severity of 1 Umm, 1? IIRC, order by in subqueries isn't even standard SQL, however this should be taken care of in current sources so that you can use order by ... limit in subqueries which sounds like what you're trying to do (although your sample code doesn't include the limit) As for temporary tables in plpgsql. If you're using 7.1, you should be able to make this work by using execute. > The lower the number the more severe it is. > > Short Description > 'order by' and 'desc' not working in subquery using 'not in' > > Long Description > Hello, > I am facing two big problems,they may not be really big but in my case these are >big : > 1) temporary tables can not be created inside function using PL/pgsql > 2) order by and desc keywords are not functioning inside subquery > > in the following query i am trying to delete all the rows except the top 20 rows >from reporttable. > How i can do this. > i have done this in MSSQL using same syntax > > Out of these two 2nd problem is important for me, > plase tell me what to do, > --- Thanks for response > > > Sample Code > delete from reporttable where (srvServerid=serverid) and > (rptreportid not in(select rptreportid from reporttable > where (srvserverid=serverid order by rpttimestamp desc))); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] ERROR: parser: parse error at or near "execute"
On Tue, 31 Jul 2001 [EMAIL PROTECTED] wrote:
> Christian Villa Real Lopes ([EMAIL PROTECTED]) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> ERROR: parser: parse error at or near "execute"
>
> Long Description I created a function to drop a table if it exists. On
> pgsql-7.0.3 this function returns an error, as follow :
>
> template1=# SELECT func_drop_table('friend');
> ERROR: parser: parse error at or near "execute"
>
> On pgsql-7.1.2 this function works fine and no error returns just the following
>lines :
IIRC, execute was added in 7.1.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] inherited tables failure
On Thu, 16 Aug 2001 [EMAIL PROTECTED] wrote: > Laszlo Csite ([EMAIL PROTECTED]) reports a bug with a severity of 1 > The lower the number the more severe it is. > > Short Description > inherited tables failure > > Long Description > We have two tables inherited one from the other one. If you try to > insert from the parent into the child by an "INSERT INTO" statement > then the record is inserted into the child but into the parent too! > Therefore in the parent duplicated rows appear. > > The other bug is if you delete a row from the parent then it erases > from the child too. see the illustration below. > create table try (col1 int4); > create table try1 () inherits (try); > insert into try (col1) values (15); > select * from try1; --> you get 0 row > select * from try; --->you get 1 row > insert into try1 select * from try; --> the answer is 1 row is inserted into try1 > > select * from try1; --> you get 1 row > select * from try; --->you get 2 row !!! <-That's wrong No, that's the intended behavior. The default behavior as of 7.1 (I believe) is that sql queries occur across inheritance trees. So the latter is select from try and any subtables. If you only want try, use ONLY (select * from ONLY try). There's only one copy of the row, however. > delete from try; > select * from try1; --> you get 0 row !!! <-That's wrong > select * from try; --->you get 0 row No, that's also intended. As above, delete from try means delete from try and any subtables and only means delete from only that table. ---(end of broadcast)--- TIP 3: 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] problem with plpgsql
On Fri, 17 Aug 2001, Pascal Bourguignon wrote: > > I've got the following problem with a plpgsql function. I believe it > denotes a bug with plpgsql. The problem is that you're trying to compare a space padded char with a non-space padded text so it's not finding the row. I believe either defining the columns as text or using rtrim(col) in the where clauses will solve your problem. ---(end of broadcast)--- TIP 3: 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: Odp: [BUGS] PD: triggered data change violation on relation"tbl_b"
This was a mistake in the interpretation of the spec (modification of the same key row referenced by a foreign key constraint in the same statement more than once is an error is how we believe the spec meant it, but there's a case where they mention transaction and it got misinterpreted). I don't think anyone's permanently fixed it yet, but making the check disappear involves commenting out the two blocks that throw the message in backend/commands/trigger.c. On Wed, 29 Aug 2001, Pawel Pawlowski wrote: > Im changing now database from Interbase 6.0 to PosgreSql 7.1.2. With IB > there is no problem to do such things. > This problem only exist when I create table tbl_a with references > (REFERENCES tbl_a (pn_id) ON UPDATE CASCADE ON DELETE CASCADE). Without this > part everything works OK. So I think that problem is with triger. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Indices get ignored in large tables
You probably need to run vacuum analyze after filling in the data. Otherwise it's using bogus selectivity (see the number of expected rows returned by the explain output, it's expecting 6000 rows after the create index, which is large enough to make it guess that it'll be cheaper to sequence scan the table. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [BUGS] postmaster quits
> Please enter a FULL description of your problem: > > > Our development server, on occaision, runs out of swap space and > inevitably when it does, the postmaster process stops. When restarted, > the following info is in the log (the machine ran out of swap around > 19:17): > > DEBUG: starting up > DEBUG: database system was interrupted at 2001-08-27 19:17:33 > DEBUG: CheckPoint record at (0, 218748312) > DEBUG: Redo record at (0, 218748312); Undo record at (0, 0); Shutdown > FALSE > DEBUG: NextTransactionId: 213780; NextOid: 297035 > DEBUG: database system was not properly shut down; automatic recovery in > progress... > DEBUG: ReadRecord: record with zero len at (0, 218748376) > DEBUG: Formatting logfile 0 seg 13 block 78 at offset 5592 > DEBUG: The last logId/logSeg is (0, 13) > DEBUG: redo is not required > DEBUG: database system is in production state > > It is odd that postgresql doesn't create a core file or retry it's > mallocs. We are running mysql and Oracle on the same machine and neither > of them have had this problem during the temporary memory shortage. It looks like you've got the logs from restarting, but is there anything from before that? Also, if you're sure that a core didn't get created in your database directory, you may need to make sure it's being started with options that allow it to create a core (check the ulimit setting). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Odp: [BUGS] PD: triggered data change violation on relation
On Mon, 3 Sep 2001, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > This was a mistake in the interpretation of the spec (modification of > > the same key row referenced by a foreign key constraint in the same > > statement more than once is an error is how we believe the spec meant > > it, but there's a case where they mention transaction and it got > > misinterpreted). I don't think anyone's permanently fixed it yet, but > > making the check disappear involves commenting out the two blocks that > > throw the message in backend/commands/trigger.c. > > Would it be better to just do that until a proper solution is > implemented? What is the downside of not making any check? I believe the intention of the check was to prevent a case from occurring where you update a value and then have a cascade constraint change it for a second time if you have some kind of recursive constraint structure. I think this could occur if you had something like TableA.id references TableB.id cascade and then TableB.id references TableA.id set default, then when you changed tableb.id, the tablea.id would change which would cause the tableb.id to be set to the default (but should error I think). This is probably a smaller failure case however, given it took some time for me to come up with a good failure, and I think more people are hitting the current problem, so I'd vote for changing it. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [BUGS] Bug #440: trigger can not find a function
> Sample Code
> CREATE FUNCTION check_key(varchar,varchar) RETURNS OPAQUE AS '
> BEGIN
> IF COUNT(*) FROM $2 WHERE $2.$1=NEW.$1 THEN
>RAISE EXCEPTION ''integrity violation : duplicated key'';
> END IF;
> RETURN NEW;
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER trigger_check_key BEFORE INSERT OR UPDATE
> ON bar
> FOR EACH ROW EXECUTE PROCEDURE check_key('id','foo');
Trigger functions take no arguments and return opaque. The arguments given
on the create trigger statement are passed in a special fashion (for plpgsql
look at TG_NARGS and TG_ARGV array).
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Bug #449: there is a problem with foreign key
When I try your script on current sources I get an error about the values being too long for the varchar(15) on some of the inserts. My guess is that on the older version the varchar(15) is truncating and making it different from the varchar(80) in the referenced table so it really is a foreign key error. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [BUGS] Bug #518: SERIAL type value not seen in FOREIGN KEY
On Wed, 21 Nov 2001 [EMAIL PROTECTED] wrote: > Edward Grabczewski ([EMAIL PROTECTED]) reports a bug with a severity >of 1 > The lower the number the more severe it is. > > Short Description > SERIAL type value not seen in FOREIGN KEY > > Long Description > I have defined a table called arch_object which contains a SERIAL type. > I have defined another table called mm_object which REFERENCES this > SERIAL type column. > I insert a value into table arch_object. This generates the next > value, which is seen using a SELECT statement. > I try to insert a value into mm_object based on this new value in > table arch_object and the INSERT is rejected. > I cannot see why as this value plainly exists in the arch_object table > and should be picked up. Foreign keys and inheritance do not currently play terribly nicely together. I think the problem is that you're trying to reference the row in deposit through arch_object which doesn't work because the foreign keys are currently explicitly referring to only that table (do a select * from only arch_object;) ---(end of broadcast)--- TIP 3: 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]
On Mon, 24 Sep 2001, Ana Claudia wrote: > Hello, > > Usually, I use Oracle for develop database application. However, I > change my computer to Linux system (Mandrake 8) and I decide change database > to Postgresql. > In Oracle when I create a table where a colum is a char(n) or varchar(n), if > I try to insert in this table value bigger than n , I get an error. That > doesn't happen in Postgresql, if I try to insert a value bigger than n, it's > accept because trunk the value. I don't want that, if the value is bigger I > want an error. I created a check constraint with the function [char_length > (parameter) < (n+1)] but still not working. Can you help me to resolve this > problem? The version of Postgresql that I have is 7.1.2. I believe this will be fixed in the upcoming 7.2 release. The reason the constraint doesn't work is probably that the truncation happens before the check. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Vá: [BUGS] Bug #519: Bug in order b
On Mon, 26 Nov 2001, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > As you see ONLY the 00CS ID is missplaced, > > the others are in correct sequence! This isn't a locale specific > > sort order. (I have RED HAT 7.1, with Hungarian settings, > > Still looks like locale to me. Exactly what LANG or LC_xxx settings > are you using for the postmaster? As a note, using a test program with LC_COLLATE=hu_HU, I get the following strcoll values. ( I'm sort of assuming that hu_HU would be the default setting for Hungary ;) ) CS <=> CY = 1 CS <=> CZ = -1 That seems to imply that for hu_HU CSs collation value is between CY and CZ. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Vá: [BUGS] Bug #519: Bug in order b
On Mon, 26 Nov 2001, Tom Lane wrote: > (HPUX is picky about locale names... 'hu_HU' doesn't work...) > This doesn't seem to entirely square with the results you two get, > but it sure does look like "CS" sorts in non-ASCII order in HU locale. I tried it with the full locale name you used and sort with that data and still got the same result as I did before (CY/CS/CZ) which seems odd that it's different, but it's on a Debian box which I'd guess should be pretty similar to the Red Hat machine of the original complaintant (sp?). ---(end of broadcast)--- TIP 3: 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: Va: [BUGS] Va: [BUGS] Bug #519: Bug in order b y clausule
On Tue, 27 Nov 2001, Sandor Vig wrote: > > > This doesn't seem to entirely square with the results you two get, > > but it sure does look like "CS" sorts in non-ASCII order in HU locale. > > I've tried to understand the "bug" and I think I have something for you: > > The Hungarian ABC (traditional old style) looks like: > A,A',B,C,CS,D,E,E',F,G...Q,R,S,T,TY, > (Yes, there are members with two characters!) > This is how we learned it in the elementary school... But nowdays > mainly the computers don't care about the two character members > of our ABC this means, that there is no "CS","TY","DZ","DZS" etc... > only "C" and "T" and so on... > > But let's say that the Red Hat uses the old style locale sort order, > but int this case should the "CS" after the "C" standing, and the > "TY" after the "T". Actually I think CS would be after C* for any C? that doesn't have a separate collation order since the additional characters don't matter. C So, it is still a mystery for me You probably need the locale for sorting the single character letters but you don't want the collation values of the multiple character ones. I think you're probably going to need to get an alternate locale file but I'm not sure what's involved in that outside of postgres. For postgres you'd need to dump, initdb under the new locale and restore probably. ---(end of broadcast)--- TIP 3: 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] text(bool), text(numeric)
On Wed, 28 Nov 2001, Vicktor wrote: > Hello All, > > Why not exists functions for convert any type to text? > I can't find text(bool), text(numeric) ... > May be it posiible in 7.2? Don't think so. You need those functions and noone has written them officially. The former is easy, something like: create function text(bool) returns text as 'select case when $1 then ''true'' else ''false'' end;' language 'sql'; should suffice. I'm not entirely sure this is entirely safe for the latter, but you can try :) create function numericout(numeric) returns int as 'numeric_out' language 'internal'; create function text(numeric) returns text as 'select textin(numericout($1));' language 'sql'; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Restoring table with array
On Mon, 8 Oct 2001, BELLON Michel wrote:
> Hi all,
>
> Restoring a table with the following file
>
> <>
> I obtain the error
> ERROR: pg_atoi: error in "{{0.22,0.2,10 ... etc
> with INSERT INTO evtransftherm etc
>
>
> +--+
> | version |
> +--+
> | PostgreSQL 7.1.2 on i686-pc-cygwin, compiled by GCC 2.95.3-4 |
> +--+
>
> and the same with 7.1.3 on a Mandrake distribution
Can you send a complete example? The given one inherits from
a non-existant table. Did you perhaps change etalverif at all?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] probably a bug of data-type serial
On Wed, 10 Oct 2001, Ralf Miller wrote: > First question: > The example shows that the column number of type serial is incremented > as if the preceding insert-statement was successfull, even if it was > not. > Are the values for the column number of type serial inserted into the > corresponding > sequence before the attempt to insert into the table and are not deleted > if the > latter fails ? > But maybe this behaviour was intended to log succcessless attempts to > insert into the table. This is intentional. If the sequence number were to roll back, then a later transaction trying to get the next sequence number would have to wait to determine if you've rolled back before getting the number. Sequences are defined to try to give unique, increasing numbers, not actually sequential ones. > Second question: > The SEQUENCE corresponding to a column of type serial is not beeing > droped > automatically when the table containing the serial-column is droped. > Does it make sense to store a sequence without a corresponding table ? The serial sequence probably should go away, but there isn't anything that would stop you from referencing the sequence somewhere else using nextval or currval. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Bug #485: strange behavior when creting rules with
On Wed, 17 Oct 2001 [EMAIL PROTECTED] wrote: > Tony Tomov ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > strange behavior when creting rules with serial id > > Long Description > I do not know if this is a bug, but for me this is a strange behavior. > I will describe the following process. Yep. Both the rule's new.id and the actual id are going to increment the counter, since they'll both see the nextval for the serial sequence, the new.id is going to shove in whatever is specified for the value exactly as it was specified, so new.id is nextval(...) not the numeric result of nextval(...). You're probably going to want to use a trigger instead. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Vá: Va: [BUGS] Va: [BUGS] Bug #
On Fri, 30 Nov 2001 [EMAIL PROTECTED] wrote: > > > So, it is still a mystery for me > > You probably need the locale for sorting the single character letters > > but you don't want the collation values of the multiple character ones. > > I think you're probably going to need to get an alternate locale > > file but I'm not sure what's involved in that outside of postgres. > > For postgres you'd need to dump, initdb under the new locale and restore > > probably. > [Vig, Sandor] > > I'll overwrite the hu_HU collocation file with the en_EN one. It is > NOT > a sollution, it just corrects the problem. Do you guys find it OK, > to > have such an effect? Should we say: > > All Hungarian -and other non standard ANSI- users should expect > "order by problems" due the locale settings.? I'm not sure this is true. As far as the system is concerned this is the correct ordering. ;) If you don't want collation based on your locale I'd suggest either turning off locale in postgres (see configure options) or initdb in "C" locale, you don't need to change the system's collation to do this, just set the locale for the shell that runs initdb. > I suggest to have -at least- a patch for such problem, or a special > postmaster switch, where an alternate collocation file could be > specified. I must work with DB2, and there is a lot of anoing side > effects > with the country selections. f.e.: No codepage translation between > Hungarian and German settings, locale-ized client full with bugs, > etc... > It would be great, not to have these things in Postgresql. There's been talk about implementing the full SQL character set stuff in the future which would probably at least limit these problems probably (it would for example presumably allow you to collate a field you didn't want to collate via hungarian rules by a different collation). ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] SQL Query Problem
On Thu, 29 Nov 2001, Kristian Lance wrote: > The server is running: > > PostgreSQL version 7.0.2 > RedHat Linux 6.2 > > When using ORDER BY in an SQL statement where the data type is varchar and > the data are unix directory paths, the forward slashes (/) are ignored, > causing the results to be returned in the incorrect order. Are you running with locale enabled and what are the LC_* settings you are starting the server with? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] index error
On Thu, 6 Dec 2001, Hao He wrote: > hi, > > If you create a table with one of the columns defined as int8, then the > index created on this column does not work. When I use explain to see > the plan, it is always seq scan instead of index scan as one would expect. > Other types of columns work fine (int4, string ...). IIRC, you need to either single quote the number or explicitly cast it into an int8 for the index to work in general, otherwise it prematurely makes it an int4 and then doesn't use the index. There've been discussions about this in the past that you can get from the archives if you're interested. (Info about this should probably be added to the FAQ if it's not there, it's pretty F) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] [GENERAL] Cant get planner to use index (7.1.3-1PGDG)
> Now, Here's where things get weird. > > fdb=> explain SELECT count(*) FROM mfps_orderinfo_435 WHERE order_date = > current_date; > NOTICE: QUERY PLAN: > > Aggregate (cost=13532.12..13532.12 rows=1 width=0) > -> Seq Scan on mfps_orderinfo_435 (cost=0.00..13528.77 rows=1340 > width=0) > > Here it does a straight date compare and it chooses not to use the index. > What?? It's probably deciding that the number of rows (1340) is large enough that the index scan isn't a win. Short form is that due to the way things are structured, after a certain point the index scan becomes more expensive than sequentially scanning the table. > fdb=> explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name = > 'SMITH'; > NOTICE: QUERY PLAN: > > Aggregate (cost=1044.16..1044.16 rows=1 width=0) > -> Index Scan using idx_mfps_orderinfo_435_fname on mfps_orderinfo_435 > (cost=0.00..1043.47 rows=279 width=0) > > EXPLAIN > fdb=> explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name > like 'SMITH%'; > NOTICE: QUERY PLAN: > > Aggregate (cost=12769.48..12769.48 rows=1 width=0) > -> Seq Scan on mfps_orderinfo_435 (cost=0.00..12769.48 rows=1 width=0) > > EXPLAIN > fdb=> explain SELECT count(*) FROM mfps_orderinfo_435 WHERE first_name > like 'SMITH'; > NOTICE: QUERY PLAN: > > Aggregate (cost=12770.17..12770.17 rows=1 width=0) > -> Seq Scan on mfps_orderinfo_435 (cost=0.00..12769.48 rows=279 width=0) > > EXPLAIN > > Here it will do an index scan if and only if I use the '=' operator. If I > use like with the % at the end of the string or EVEN if I have no wild card > at all... it still does a seq scan. If anyone has any advice on how to > get these indexes working properly, please let me know. You may want to check your locale setting. IIRC, if you're running with locale enabled and not in C locale, LIKE does not get optimized to run with indexes. ---(end of broadcast)--- TIP 3: 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 #537: In Red Hat don't open port 5432 and PostgreSql
On Thu, 13 Dec 2001 [EMAIL PROTECTED] wrote: > Roger ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > In Red Hat don't open port 5432 and PostgreSql don't work. > > Long Description > Hi! > I recently installing Red Hat 7.2. One of RPM were your PostgreSql. I > thing that there is a bug in the instalallation. Packet is installed > correctly with no error messages. When I try to init server by > (etc/init.d/postgresql start) the Linux says all is OK. But when I > make a port Scan the PostfreSql Port (5432) is not openened and > PostMaster process is running. So PostgreSql don't work because I can > not connect to databases... In redHat 7.1 all was ok But 7.2 What version of PostgreSQL is it? I believe between PostgreSQL 7.0 and PostgreSQL 7.1 the default was changed on install to not open the port for security reasons. You'll probably need to edit the postgresql.conf file to turn it on (tcpip_socket = true) and may need to edit the pg_hba.conf to set up your security rules. ---(end of broadcast)--- TIP 3: 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 #539: Unexpected DeadLock on REFERENCES
On Fri, 14 Dec 2001 [EMAIL PROTECTED] wrote: > Eugene Fokin ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > Unexpected DeadLock on REFERENCES > > Long Description > DeadLock happens when we're trying to concurrent update different > tables which chained with one table through REFERENCE. > See Example Code. Just perform the script and follow instructions > on the header. > This case have been checked on 7.1.3 version. > > Sample Code > -- > -- At first you should execute this script. > -- In two different sessions for one DB perform: > -- (1) begin transaction; > -- (2) begin transaction; > -- (1) update ref1 set d='R0'; > -- (2) update ref2 set d='R0'; > -- (1) update ref3 set d='R0'; > -- (2) update ref4 set d='R0'; > -- !!! Didn't you get DeadLock ? I get a detected deadlock. This is a known problem due to the foreign key locks being stronger than necessary. I think a shared row lock that blocks update/delete but not itself rather than the current select for update lock would allow this case to work. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #540: pg_dump a simple table can fail if other
On Sun, 16 Dec 2001 [EMAIL PROTECTED] wrote: > luby liao ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > pg_dump a simple table can fail if other view/triggers are faulty > > Long Description > We use Postgresql 7.1.2 on a Redhat 7.0 Linux PC. My CS145 students > all work in a database called cs145. Some students introduced > unintended faulty views and triggers into the database. When a > student tried to pg_dump his own tables, he got error messages that > say that other people's views or triggers were faulty. We're going to need some additional information for this to be a complete report. What options were used to pg_dump, how far the dump got and what messages were reported. It'd be nice to know what was meant by faulty views and triggers or an example to reproduce the problem. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] case-sensitivity inconsistency in quoted column aliases
On Wed, 9 Jan 2002, Mike Hoolehan wrote: > Please enter a FULL description of your problem: > > if a quoted column alias in a FROM clause sub-select contains upper-case > chars, then that column cannot be later referenced without using quotes > > Please describe a way to repeat the problem. Please try to provide a > concise reproducible example, if at all possible: > -- > SELECT * FROM (SELECT col1 as "Foo" from table1) AS innerQuery > WHERE Foo = 'whatever'; > results in > "ERROR: Attribute 'foo' not found" > no matter what capitalization is used for "Foo" in the where clause > (i.e. foo='whatever', FOO='whatever', etc). > I assume this is a bug, since if the column is aliases as "foo" (with > quotes, all lowercase), then the column can later be referenced > without quotes with case-insensitivity. Unquoted column and table names are folded to lower case (thus Foo is the same as foo or FOO or "foo", but "Foo" is different). In general, although "foo" will be matched, if you use quotes around the name in its creation you should always use quotes thereafter. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] 'natural join' and 'join ... using' giving different
On Wed, 9 Jan 2002, Bruno Wolff III wrote: > I am seeing different results when using 'natural join' as opposed to > 'join ... using' on what I think the equivalent columns should be. > The 'join ... using' version of the query gives the expected answer. > I have tried this on 7.1.3 (built locally) and 7.2b4 with a patch > to how foreign keys are checked. The machines where both running > Redhat linux 6.1 with a 2.2.16 kernel. > > Below is a sample psql session that I think illustrates the problem. Wouldn't games natural join crate be on both gameid and touched as opposed to only gameid? I don't remember exactly but natural join joins on columns of the same name right? > > Script started on Wed Jan 9 11:17:06 2002 > [bruno@wolff bruno]$ psql > Welcome to psql, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms >\h for help with SQL commands >\? for help on internal slash commands >\g or terminate with semicolon to execute query >\q to quit > > area=> \d games > Table "games" > Attribute | Type |Modifier > ---+--+ > gameid| text | not null > title | text | not null > touched | timestamp with time zone | not null default 'now' > Indices: games_pkey, > title_idx > Constraints: (gameid ~ '^[A-Z0-9]+$'::text) > (title ~ '^[!-~]+( [!-~]+)*$'::text) > > area=> \d crate > Table "crate" > Attribute | Type |Modifier > ---+--+ > areaid| text | not null > gameid| text | not null > rate | integer | not null default 5000 > frq | integer | not null default 0 > opp | integer | not null default 0 > rmp | integer | not null default 0 > trn | integer | not null default 0 > touched | timestamp with time zone | not null default 'now' ---(end of broadcast)--- TIP 3: 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 #569: Strange Queries Behaviours
On Tue, 29 Jan 2002 [EMAIL PROTECTED] wrote: > Carlos Atencio ([EMAIL PROTECTED]) reports a bug with a severity of 2 > > I've this query that I run in my app. For example, give a list of > all sales from all my stores around the country. The query runs very > fast and I get the result I want. Later, I try to reduce the search > and instead of list all stores, I want to list only one store. The > query runs but this time is slower than before (I have the proper > indexes in that table). In this case, we're using the same query but > with one more condition. > > I looked at the query with the explain command and I saw that > Postgres wasn't using the sentence where I ask only for one store. > Accidentally, I made a mistake and wrote the one store sentence twice > in that query. My surprise was that the query did work!!!. When the > query had the same sentence written twice it actually worked fine and > it took fewer time than before. > > None has known how to repair this. In some queries, duplicating > some senteces makes it work. In other queries, it doesn't. We're probably going to need more information. At least schema, queries and explain output will be useful. You may also want to try your setup on 7.1.3 and see if it runs better. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] strange bug with vacuum
On Fri, 1 Feb 2002, hubert depesz lubaczewski wrote: > Operating system: > Linux (PLD), kernel 2.2.20 > > Hardware: > $ cat /proc/cpuinfo | grep -E "^(vendor_id|model name|cpu MHz)" > vendor_id : GenuineIntel > model name : Pentium III (Coppermine) > cpu MHz : 871.474 > > $ free > total used free sharedbufferscached > Mem:387712 385028 2684 79864 146396179892 > -/+ buffers/cache: 58740 328972 > Swap: 524624 0 524624 > > PostgreSQL version: > PostgreSQL 7.2b4 on i686-pc-linux-gnu, compiled by GCC 2.95.4 > > > it appears that doing vacuum analyze just after creation of tables and > indices permanently damages indices. > our code made something like: > create table(); > create index .. > vacuum analyze; > now apporximatelly ca 10 inserts, each prepended by > select id from table where field = value; > field is of type int8 (value also, and we do casting to int8) and there > is unique index on table.field; > every 5000 inserts we do vacuum analyze of this table. > the problem is that the time system spends on mentioned selects raises > linear way instead of (expected) logarythmical. > > we checked twice that *not* making initial vacuum (while still doing > vacuum analyze every 5000 inserts) does modify this behaviour to much > more reasonable. > > is this something we are missing or another bug in postgresql? > > the selects and inserts are made through plpgsql function (single call > select function(args) does select and insert), but i doubt if this > matters. Well the initial vacuum analyze is going to set up sequence scan as the best plan. And yep, it does matter that it's in a function :) The query plan is going to be saved unless you use execute which means it saves the plan from the empty analyze (ie seq scan wins) at least until you start a new session. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] plpgsql support
On Tue, 29 Jan 2002, Nagasubramanya L wrote: > I have even tried > CREATE LANGUAGE 'plpgsql' > > But I am not able to set it. What is that I should do to set up this language >support. Use the shell script createlang createlang plpgsql should add it to the database in question. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Bug #578: pg_dumpall from 7.1.3 can not be imported in
On Fri, 8 Feb 2002 [EMAIL PROTECTED] wrote: > 2. tables with complex foreign key constrains will not be imported I probably couldn't help with the other question, but... What do you mean by this? Does it error on the creation, does it make the tables but not the constraints? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug #581: Sequence cannot be deleted
On Mon, 11 Feb 2002 [EMAIL PROTECTED] wrote: > BAZIN Nicolas ([EMAIL PROTECTED]) reports a bug with a severity of 1 > The lower the number the more severe it is. > > Short Description > Sequence cannot be deleted > > Long Description A Sequence is created automatically with the SQL > command: > CREATE TABLE fa_ccpsholderscpt(hsc_serial SERIAL NOT NULL ,chd_serial > INTEGER NOT NULL ,hsc_respcode CHAR(2) NOT NULL ,scp_code CHAR(4) NOT > NULL ,imp_flag SMALLINT) > > but when I try to delete it with the following command: > DROP SEQUENCE fa_ccpsholderscpt_hsc_serial_seq That's not the name of the sequence in question unless you've upped the number of characters in an identifier. The sequence appears to on my machine be named "fa_ccpsholderscp_hsc_serial_seq" because the name would have ended up being too long. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] BUG: bigint and indexes ?
On Fri, 1 Mar 2002, Victor Krasinsky wrote: > Is it a known bug if optimizer, that Postgres can't use indexes created on a int8 >(bigint) ? > This problem exists in 7.1.3 and even in 7.2. You need to explicitly cast the constant that you're comparing to bigint (1::bigint or CAST(1 as bigint)). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
