Re: [BUGS] BUG #4533: Plpgsql complex type failure
That was the short form exhibiting the bug. Selecting the complex column from a table into a value results in the same error. BEGIN; CREATE TYPE timestamp_with_precision AS ( timestamp timestamp with time zone , timestamp_precision varchar ); CREATE TABLE timetest(t timestamp_with_precision); INSERT INTO timetest VALUES ((CURRENT_DATE, 'day')::timestamp_with_precision); CREATE OR REPLACE FUNCTION testfunc() RETURNS boolean AS $FUNC$ DECLARE v_time timestamp_with_precision; BEGIN SELECT t INTO v_time FROM timetest; RETURN true; END; $FUNC$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER RETURNS NULL ON NULL INPUT; SELECT testfunc(); ROLLBACK; On Sat, Nov 22, 2008 at 1:37 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Rod Taylor" <[EMAIL PROTECTED]> writes: >> DECLARE >> v_time timestamp_with_precision; >> BEGIN >> SELECT (CURRENT_DATE, 'day')::timestamp_with_precision >> INTO v_time; > > You're overthinking the problem, it should just read > > SELECT CURRENT_DATE, 'day' >INTO v_time; > >regards, tom lane > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4533: Plpgsql complex type failure
The following bug has been logged online:
Bug reference: 4533
Logged by: Rod Taylor
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.3.5
Operating system: FreeBSD 7.0 Stable
Description:Plpgsql complex type failure
Details:
The below script should be able to correctly set v_time to the complex type
timestamp_with_precision. It seems to take it as "timestamp with time zone"
instead creating interesting errors like this:
BEGIN
CREATE TYPE
CREATE FUNCTION
psql:/home/rbt/foo.sql:19: ERROR: invalid input syntax for type timestamp
with time zone: "("2008-11-16 00:00:00-05",day)"
CONTEXT: PL/pgSQL function "testfunc" line 4 at SQL statement
ROLLBACK
Note, the below continues to fail in the same way using
timestamp_with_precision%ROWTYPE.
BEGIN;
CREATE TYPE timestamp_with_precision AS
( timestamp timestamp with time zone
, timestamp_precision varchar
);
CREATE OR REPLACE FUNCTION testfunc() RETURNS boolean AS $FUNC$
DECLARE
v_time timestamp_with_precision;
BEGIN
SELECT (CURRENT_DATE, 'day')::timestamp_with_precision
INTO v_time;
RETURN true;
END;
$FUNC$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER RETURNS NULL ON NULL
INPUT;
SELECT testfunc();
ROLLBACK;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4182: Enum in Foreign Key broken
The following bug has been logged online:
Bug reference: 4182
Logged by: Rod Taylor
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.3.1
Operating system: amd64-portbld-freebsd7.0, compiled by GCC cc (GCC) 4.2.1
20070719 [FreeBSD]
Description:Enum in Foreign Key broken
Details:
The Update in the below script results in the following
ERROR: no conversion function from some_enum to anyenum
CREATE TYPE some_enum AS ENUM('Value 1', 'Value 2');
CREATE TABLE t1
( col1 varchar(10)
, col2 some_enum
, testboolean boolean
, PRIMARY KEY(col1, col2)
);
CREATE TABLE t2
( col1 varchar(10)
, col2 some_enum
, PRIMARY KEY(col1, col2)
, FOREIGN KEY (col1, col2) REFERENCES t1(col1, col2) on update cascade on
delete restrict
);
INSERT INTO t1 VALUES ('varcharval', 'Value 1');
INSERT INTO t1 VALUES ('varcharval', 'Value 2');
INSERT INTO t2 VALUES ('varcharval', 'Value 1');
UPDATE t1 SET testboolean = false;
-- ERROR: no conversion function from some_enum to anyenum
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] [BUGS] BUG #1290: Default value and ALTER...TYPE
On Wed, 2004-10-20 at 14:07, Tom Lane wrote: > "PostgreSQL Bugs List" <[EMAIL PROTECTED]> writes: > > troels=# create table lookat_feature( > > troels(# feature_id char(4), > > troels(# status varchar(2) default 'TODO' > The alternative would seem to be decreeing that this is not a bug. > > Comments anyone? I think the bug is that default takes a value which does not fit the columns data type. bric=# create table test (col numeric(6,2) DEFAULT '1023456632'); CREATE TABLE bric=# insert into test default values; ERROR: numeric field overflow DETAIL: The absolute value is greater than or equal to 10^9 for field with precision 6, scale 2. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] CREATE INDEX spoils IndexScan planns
> TRACE: > The original tables are much bigger, so I've tried to simplify things here. > Please let me know if there is anything that I could help you with. You can't do that and expect to get reasonable results. The plans will change with the volume of data. Send an explain analyze of the true problem after ANALYZE with seqscan on and one with it off. Thanks. signature.asc Description: This is a digitally signed message part
Re: [BUGS] minor: ~ not resolved in psql
On Thu, 2003-10-30 at 13:13, Peter Eisentraut wrote: > Bas Scheffers writes: > > > When opening a file in psql, ~ (the user's home dir) is resolved when > > using tab completion to find the file, but when you hit enter and psql > > actualy tries to open it, you get a file not found error. > > This is not a bug. If there is not file named ~ then it can't be opened. If the file doesn't exist, then surely tab completion shouldn't pick it up either. \i ~/.b <- picks up files After finishing a name, hit enter -- the file could not be found. signature.asc Description: This is a digitally signed message part
Re: [BUGS] [HACKERS] Autocomplete on Postgres7.4beta5 not
On Tue, 2003-10-28 at 19:34, scott.marlowe wrote: > On Tue, 28 Oct 2003, Tom Lane wrote: > > > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > > Is it possible to remove the implicit search path of pg_catalog from a > > > psql session without it breaking lots of stuff? > > > > Do you consider "+", "count()", etc to be important stuff? > > Me, hardly ever use them :-) So I can assume that removing the implicit > pg_catalog from the search path is a "bad thing." From the search_path certainly -- but we can we teach the difference between implicit and explicit to the *is_visible functions? signature.asc Description: This is a digitally signed message part
Re: [BUGS] [HACKERS] Autocomplete on Postgres7.4beta5 not
On Tue, 2003-10-28 at 18:49, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > I say leave it the way it is. If you want system table tab completion, > > simply: > > ALTER USER ... SET search_path =3D pg_catalog,...; > > Unfortunately, that *does not* affect the tab-completion behavior; > it will still not offer the system catalogs as completions unless > you explicitly prefix "pg_catalog.". Very well.. Would it be enough simply to fix it so it does work? Remove the S functionality and allow pg_catalog to work like a normal schema. signature.asc Description: This is a digitally signed message part
Re: [BUGS] [HACKERS] Autocomplete on Postgres7.4beta5 not working?
> Anyway, it seems like we need a vote to see how many people prefer > each choice. I say leave it the way it is. If you want system table tab completion, simply: ALTER USER ... SET search_path = pg_catalog,...; I would like to see the information_schema be a part of the default search_path -- but I can do that myself. signature.asc Description: This is a digitally signed message part
Re: [BUGS] minor: ~ not resolved in psql
> Please enter a FULL description of your problem: > > When opening a file in psql, ~ (the user's home dir) is resolved when > using tab completion to find the file, but when you hit enter and psql > actualy tries to open it, you get a file not found error. This is an issue in 7.4 as well. signature.asc Description: This is a digitally signed message part
Re: [BUGS] Table alias fun == incorrectly adding tables to join...
> NOTICE: adding missing FROM-clause entry for table "table_s1" > NOTICE: adding missing FROM-clause entry for table "table_s2" > and execution times explode, naturally. I don't know if the spec says > this is the correct behavior or not, but I have a feeling its not and > this is a bug. This is a PostgreSQL extension to make it easier on those using the database. In fact, it's the only way to currently write a delete using a join. In 7.4 you can disable this extension in postgresql.conf (add_missing_from = false) signature.asc Description: This is a digitally signed message part
Re: [BUGS] select where id=random()*something returns two results
On Sun, 2003-09-21 at 08:21, Ulrich Meis wrote: > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:pgsql-bugs- > > [EMAIL PROTECTED] On Behalf Of Jean-Luc Lachance > > Sent: Friday, September 19, 2003 4:44 PM > > To: Rod Taylor > > Cc: Ulrich Meis; [EMAIL PROTECTED] > > Subject: Re: [BUGS] select where id=random()*something returns two > results > > > > Rod, > > > > If the table has 100,000 tupples your query is generating 100,000 new > > tupples... > > Try: > > > > select * from quotes where id = ( > > select int8( 1 + random() * ( > > select id from quotes order by id desc limit 1))); > > > > How about > > select * from quotes where id=1+int8((select random())*(select max(id) > from quotes)); > > It works, but is it more or less efficient? Run EXPLAIN ANALYZE on them both and you tell me which is more efficient. Efficiency of a query tends to change with the data that it is being executed on. signature.asc Description: This is a digitally signed message part
Re: [BUGS] select where id=random()*something returns two results
> select * from quotes where id=1+round(random()* cast ((select max(id) > from quotes) as double precision)); > id | quote | > author > -+---+-- > --- > 187 | Vergib Deinen Feinden, aber vergiss niemals ihre Namen. | John > F. Kennedy > 377 | Die Wirklichkeit ist nicht so oder so, sondern so und so. | Harry > Mulisch > (2 rows) > > I'm not really into databases, but this sounds wrong. Most of the time, > I actually get 0 results. Random is calculated per call (in this case per comparison). So, the value you compare against for 187 is not the same as 377. UPDATE table SET column = random(); will show the effect. If you wrap randon() in a subselect, it will cause it to be evaluated once: SELECT * from quotes where id = 1+round((SELECT random()) * cast(). However, a much faster query for your purposes would be: SELECT * FROM quotes ORDER BY random() LIMIT 1; signature.asc Description: This is a digitally signed message part
Re: [BUGS] Is it bug???
On Fri, 2003-09-05 at 09:18, Bruno Wolff III wrote: > On Wed, Sep 03, 2003 at 23:19:24 -0700, > Ringo <[EMAIL PROTECTED]> wrote: > > What a prick.. > > >From observing Tom's responses here, I think this is a real question (not > an RTFM response). If the documentation doesn't make it clear to new readers > that explain analyze does execute the query (though doesn't return the > results), then the documentation needs to get fixed. I see 3 places on the EXPLAIN page that states ANALYZE queries are executed. Perhaps it should be noted elsewhere, but it is well described in the "SQL Commands" section of the docs. One with the ANALYZE keyword under 'Inputs': Flag to carry out the query and show actual run times. One in the description: The ANALYZE option causes the query to be actually executed, not only planned. The total elapsed time expended within each plan node (in milliseconds) and total number of rows it actually returned are added to the display. This is useful for seeing whether the planner's estimates are close to reality. Finally, one in a big CAUTION block near the bottom of the description: Keep in mind that the query is actually executed when ANALYZE is used. Although EXPLAIN will discard any output that a SELECT would return, other side-effects of the query will happen as usual. If you wish to use EXPLAIN ANALYZE on an INSERT, UPDATE, or DELETE query without letting the query affect your data, use this approach: BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; signature.asc Description: This is a digitally signed message part
Re: [BUGS] Postgre connection management
On Thu, 2003-08-21 at 07:55, Annu Panesar wrote: > hi > i need help urgently to understand how postgres manages its connection > pool , how it manages deadlock . we are using postgres See http://www.postgresql.org/docs/7.3/static/explicit-locking.html Section 9.3.3 for DeadLocks. signature.asc Description: This is a digitally signed message part
Re: [BUGS] UNION discards indentical rows in postgres 7.3.3
> Wrong ! The query should return 4 rows. In other words i don't know > why postgres performs the following query: I think the syntax you're looking for is UNION ALL. select a,b from t1 union all (select distinct a,b from t2); signature.asc Description: This is a digitally signed message part
Re: [BUGS] 7.3.3 ADD COLUMN wierdness
On Mon, 2003-06-16 at 07:53, Brian O'Donoghue wrote: > I'm running two different strains of PostgreSQL > > 7.1.3, which is what our website is running off of now and 7.3.3, which we > hope to move to (sometime). > > In 7.1.3 I can say > > ALTER TABLE blah ADD COLUMN whatever integer not null; CREATE TABLE bob(col integer); INSERT INTO bob VALUES (1); ALTER TABLE bob ADD COLUMN col2 integer NOT NULL; Now of course, the table has a NULL value for col2 which is not allowed per the NOT NULL constraint. Until we allow processing of a DEFAULT expression during column creation, this will not work that way. You could argue we should allow NOT NULL to be added to an empty table. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [BUGS] Index speeds up one row table (why)?
> vacuum verbose bigint_unique_ids; > INFO: --Relation public.bigint_unique_ids-- > INFO: Index bigint_unique_ids__table_name: Pages 29; Tuples 1: Deleted > 5354. > CPU 0.01s/0.04u sec elapsed 0.05 sec. > INFO: Removed 11348 tuples in 79 pages. > CPU 0.00s/0.02u sec elapsed 0.02 sec. > INFO: Pages 79: Changed 1, Empty 0; Tup 1: Vac 11348, Keep 0, UnUsed 0. > Total CPU 0.03s/0.06u sec elapsed 0.14 sec. Vacuum (regular, not full) frequently enough that the 'Pages' value doesn't increase past 1 and you'll be fine. A sequential scan on a very small table is what you want to have. In this particular case, vacuum removed over 11000 dead versions of the tuple. An 8 k page will hold approx 140 tuples based on your structure. So, for every ~100 updates you'll want to run vacuum (regular, not full) on the table. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [BUGS] Bug #918: pg_dump problem w/ SEQUENCE
It's my bad. Has to do with the sequence having the default value and *NOT* having been called. See -patches for update to simply skip START WITH in that case. On Tue, 2003-03-25 at 10:08, Bruce Momjian wrote: > I just did 'CREATE SEQUENCE x' and pg_dump produced: > > CREATE SEQUENCE x > START WITH 1 > INCREMENT BY 1 > NO MAXVALUE > NO MINVALUE > CACHE 1; > > which has the proper value after START WITH. Any ideas why your setup > is different? > > --- > > [EMAIL PROTECTED] wrote: > > laurent faillie ([EMAIL PROTECTED]) reports a bug with a severity of 2 > > The lower the number the more severe it is. > > > > Short Description > > pg_dump problem w/ SEQUENCE > > > > Long Description > > Hi all, > > > > in order to correct bug #899, I have downloaded the current snapshot and I > > discover a new trouble w/ pg_dump. > > > > If you have a sequence in your database, pg_dump produce something like : > > > > CREATE SEQUENCE seq_id_fch > > START WITH > > INCREMENT BY 1 > > MAXVALUE 2147483647 > > NO MINVALUE > > CACHE 1; > > > > and psql rise following error : > > > > ERROR: parser: parse error at or near "WITH" at character 38 > > > > PS: I have downloaded the snapshot monday 24/03/2003 > > > > Bye > > > > Laurent > > > > > > Sample Code > > > > > > No file was uploaded with this report > > > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [BUGS] Bug #880: COMMENT ON DATABASE depends on current
> > I tend to think that the functionality to give comments to databases > > should either be redone to work right (for example by storing the comment > > in a global table (but think about the encoding problems)) or be ripped > > out. Right now the feature to give a comment to a database you presumably > > already know (since you connected to it) does not seem to justify the > > confusion it causes. > > Good analysis. Is removal actually the best solution? Front-ends like pg_admin actually make pretty good use out of it. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [BUGS] Bug #880: COMMENT ON DATABASE depends on current
On Fri, 2003-01-24 at 06:16, Bhuvan A wrote: > > Long Description PostgreSQL has mechanism for commenting databases. > > Database comments can by read by obj_description(oid), psql \l+ command > > use it. Database comments should be global, but they are not, when we do > > \l+ on one database, and then on other, results will be different. I > > consider it is a bug, database is global object (You can connect to it > > from any database) but their comments are not. > > I too consider it as a bug. But why no response? I hope someone is > hearing. It has been discussed in the past: http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&safe=off&threadm=04f201c1e7cc%243479b2a0%248001a8c0%40jester&rnum=1&prev=/groups%3Fq%3Dcomment.on.database%2Bgroup:comp.databases.postgresql.hackers%26hl%3Den%26lr%3Dlang_en%26ie%3DUTF-8%26oe%3DUTF-8%26safe%3Doff%26selm%3D04f201c1e7cc%25243479b2a0%25248001a8c0%2540jester%26rnum%3D1 -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [BUGS] Bug #867: CLUSTER does not rebuild referential
Upgrade to 7.3. Cluster has been redesigned to be much more user friendly -- not to mention transaction safe. On Wed, 2003-01-08 at 15:21, [EMAIL PROTECTED] wrote: > Boris Folgmann ([EMAIL PROTECTED]) reports a bug with a severity of 3 > The lower the number the more severe it is. > > Short Description > CLUSTER does not rebuild referential integrity triggers > > Long Description > I've got the following behaviour using pgsql 7.2.2 on Linux: > > When clustering a table with CLUSTER I get > NOTICE: DROP TABLE implicitly drops referential integrity trigger from table >for every RI_ConstraintTrigger. The bad thing is that the Trigger is not being >reconstructed when the CLUSTER is finished. > > > Sample Code > \d table_with_ri_triggers > cluster some_index on table_with_ri_triggers > \d table_with_ri_triggers > > -> see that ri_triggers are vanished. > > > No file was uploaded with this report > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [BUGS] contrib/adddepend does not properly re-create
On Sun, 2002-12-01 at 18:57, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > Adams patch is good. Please apply when you find time. > > I'm a bit confused by the patch --- it has a patch against "upgrade.pl" > which is not to be seen in contrib/adddepend. What's up with that? It seems to be against the version I originally posted. The second, adddepend.patch is what should be applied to the contrib/adddepend/adddepend file -- Rod Taylor <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
Re: [BUGS] contrib/adddepend does not properly re-create
On Sun, 2002-12-01 at 14:11, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > It makes sense, but I've not tested it yet. > > Okay, I'll hold off applying until you check it. It's not urgent, > but I'd like to have it fixed in 7.3.1, which we'll doubtless be > wanting to roll out in a few weeks. Adams patch is good. Please apply when you find time. -- Rod Taylor <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
Re: [BUGS] contrib/adddepend does not properly re-create
It makes sense, but I've not tested it yet. On Sun, 2002-12-01 at 14:06, Tom Lane wrote: > Adam Buraczewski <[EMAIL PROTECTED]> writes: > > Simply: the program takes column names from the wrong end of the array > > :) > > > I already notified the author of the program and attached patches to > > both original upgrade.pl and contrib/adddepend scripts. > > Rod, do you concur that this is a correct patch? -- Rod Taylor <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
Re: [BUGS] DROP COLUMN really work?
Sounds to me like your using an old version of psql with the new database -- it will show the stale columns, but they're not really there. On Sun, 2002-12-01 at 07:27, Ruslan A Dautkhanov wrote: > Hi all, > > > isbs=# select version(); > version > --- >PostgreSQL 7.3 on i386-unknown-freebsd4.7, compiled by GCC 2.95.4 > (1 row) > > isbs=# create table abba (x int4, y int4); > CREATE TABLE > isbs=# \d abba > Table "abba" >Column | Type | Modifiers > +-+--- >x | integer | >y | integer | > > isbs=# alter table abba drop column x; > ALTER TABLE > isbs=# \d abba > Table "abba" > Column| Type | Modifiers > --+-+--- >pg.dropped.1 | integer | >y| integer | > > > Why the first column not really deleted from table? How to make it work? > I tries using VACUUM FULL abba, but this didn't help. Thanks a lot for comments. > > > P.S. btw, I upgrade my DBs from 7.2.1 to 7.3 version without any problems, but > found, that psql's help messages (\h, \h alter table etc.) not from 7.3 > version. For example, it do not contain \h CREATE SCHEMA help message. > > > -- > best regards, > Ruslan A Dautkhanov [EMAIL PROTECTED] -- Rod Taylor <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
Re: [BUGS] regression hickups w/ freebsd.
Yup.. 4.0 - 4.5 have positive zeros. 4.6 has negative zeros 4.7 release has positive zeros but shortly after -stable has negative zeros. It's difficult to differentiate between 4.7-release and 4.7-stable, so the regression tests were made for -stable. On Tue, 2002-11-26 at 15:59, bpalmer wrote: > I seem to be having some regression problems w/ freebsd for both rc1 and > rc2. > > They are the old 0 / -0 problems. I've attached the regression diff. > > $uname -an > FreeBSD farcaster 4.7-RELEASE FreeBSD 4.7-RELEASE #1: Sat Nov 16 21:55:12 > EST 2002 root@farcaster:/usr/src/sys/compile/FARCASTER i386 > > both regression test (rc1 and rc2) are the same. > > - Brandon > > > > > c: 917-697-8665h: 201-798-4983 > b. palmer, [EMAIL PROTECTED] pgp:crimelabs.net/bpalmer.pgp5 > > > __ > > ---(end of broadcast)------- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rod Taylor <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] RULE: ON DELETE doesn't stack deletes
Confirmed this problem on cvs-tip. Replacing the DO INSTEAD ( DELETE) with DO INSTEAD (INSERT...) allows multiple insert statements which function fine using OLD.a_data and OLD.b_data. So it must be something else. Could it be because once the DELETE FROM A has run the tuple no longer exists in the view? On Wed, 2002-11-20 at 10:49, Malcolm Hutty wrote: > According to the manual you can stack multiple queries in a RULE: > CREATE RULE R_DEL_AB AS > ON DELETE TO V_AB > DO INSTEAD > ( > DELETE FROM A WHERE a_data=OLD.a_data; > DELETE FROM B WHERE b_data=OLD.b_data; > ); -- Rod Taylor <[EMAIL PROTECTED]> ---(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] vacumm error
On Fri, 2002-11-22 at 15:51, Stefanos Harhalakis wrote: > I'm running postgresql 7.2.1 on linux. > > I cannot run vacuumm on a table in a database i'm running for about 7 months. > I get: > > ERROR: No one parent tuple was found Your best bet, since you've crashed with fsync off, is to dump and reload the database. This should clean up any data issues you currently have -- assuming pg_dump functions on it. That said, 7.2.3 has several vacuum fixes, but I don't think they'll fix your problem. -- Rod Taylor <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] PostgreSQL No Longer Handles Mixed Case Sequences
On Wed, 2002-11-13 at 07:51, Brian Harris wrote:
> Hello,
> I have just updated to PostgreSQL 7.2.2. I am utilising tables with
> mixed case table and field names. I can now no longer insert data into
> these tables as shown by the following psql interchange:
>
> sharetrack=> \d "T_Joint"
> Table "T_Joint"
>
> Column | Type |Modifiers
> -+---+--
> id | integer | not null default nextval('T_Joint_id_seq'::text)
> Name | Type | Owner
> -+--+---
> T_Joint_id_seq | sequence | brian
> Please can you advise if there is a fix available or any circumvention
> apart from redefining all affected tables.
You need to either:
ALTER TABLE RENAME "T_Tester_id_seq" TO t_tester_id_seq;
Or
ALTER TABLE "T_Joint"
ALTER COLUMN id
SET DEFAULT nextval('"T_Tester_id_seq"'::text);
The first converts the case of the sequence name to lower, the second
has nextval use the uppercase version.
--
Rod Taylor <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Bug in pg_dump
> If you know how this problem might be fixed, list the solution below: > - > > Dump all functions and types before indexes This causes errors in other ways. The true solution is to have pg_dump use a decent dependency tracking mechanism. 7.4 should hold the fix for pg_dump, although 7.3 has the dependency tracking mechanism. -- Rod Taylor <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] I have problem with Postgres.
On Thu, 2002-11-14 at 03:40, ðÏÐÏ× áÎÄÒÅÊ wrote:
> Hi.
> I have problem with Postgres.
> I have perl script, that add 2 records in 2 tables. This script I run
> from web throught web server Apache (on FreeBSD).
> (perl version 5.005)
You would be better off using the sequence rather than trying to fetch
out max(obj_id).
select currval('object_list_obj_id_seq') AS nextvalue;
after the insert into object_list.
>
> $sth = $dbh->prepare("select max(obj_id) from object_list");
> $sth->execute();
> $max = $sth->fetchrow;
--
Rod Taylor <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #790: Optimizer does not want to use an index for
On Sun, 2002-09-29 at 06:25, [EMAIL PROTECTED] wrote: > Jekabs Andrushaitis ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > Optimizer does not want to use an index for large table > > Long Description > I have a table with large number of records (10). Data from this table is mostly >selected using one field, on which there is an index. > Explain plan does not show that query engine will use this index, however, which is >odd, since statistics clearly show that index fits in a single page, while whole >table is about 600 pages. The most common select on the table would retrieve about 10 >rows of data, and using the index would be way more efficient. I read the >documentation describing indexes, explain plans and how the statistics affects >queries, and according to docs, the index should have been used...but Explain shows >that it's not. I tried dropping and re-creating the index, but still it's not used! > I am usine PostgreSQL 7.2 on Linux Mandrake. > > Sample Code > CREATE TABLE obj_props(obj_id int8,name text,value text); > > for i:=0 to 1 > for j:=0 to 10 > INSERT INTO obj_props(i,'some name','some value'); > > CREATE INDEX obj_props_ind1 ON obj_props(obj_id); > > VACUUM ANALYZE; > > EXPLAIN SELECT name,value FROM obj_props WHERE obj_id=100; This is one of those annoying things that has recently been (or is in the process of being) fixed in 7.3. End result, obj_id is an int8, while 100 is an int4. Older versions don't do the cast of int4 to int8 as there was the int48eq operator available and some logic missing to cast in the right direction. For 7.2, use obj_id='100'. Quotes make the entry UNKNOWN which is casted to int8 and should use the index. -- Rod Taylor ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Bug #774: Sometimes we do not get the correct record set
Could you attach an sql test case that shows the error you see, or a detailed description of how to recreate it? On Thu, 2002-09-19 at 03:55, [EMAIL PROTECTED] wrote: > Michelle ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > Sometimes we do not get the correct record set > > Long Description > We did a select statement to select a recordset from the database and the recordset >size differs. > For example, there are a total of 10 records in the database. > When my program do a select query for a few times, the recordset size is 10. But >sometimes the recordset size may only be 4. > What could the problem be? > > > Sample Code > > > No file was uploaded with this report > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Rod Taylor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #772: Rewriting on multi-record updates is
On Wed, 2002-09-18 at 17:08, Anto Prijosoesilo wrote: > I was afraid that you're going to say that (use > triggers) :-). > > I'm trying _not_ to use triggers because rules are > more maintainable than triggers here in my > environment. At least with rules someone who knows SQL > would be able to maintain it with just a little extra > training. > > The PostgreSQL 7.2 Reference Manual, in the section > for CREATE RULE seems to imply that the rules are > executed per row of the target table. I'm referring to > paragraph 2 of the description. Read the last paragraph of that section: It is important to realize that a rule is really a query transformation mechanism, or query macro. The entire query is processed to convert it into a series of queries that include the rule actions. This occurs before evaluation of the query starts. So, conditional rules are handled by adding the rule condition to the WHERE clause of the action(s) derived from the rule. The above description of a rule as an operation that executes for each row is thus somewhat misleading. If you actually want an operation that fires independently for each physical row, you probably want to use a trigger not a rule. Rules are most useful for situations that call for transforming entire queries independently of the specific data being handled. Not that the description is obvious, but it's there. Perhaps you know of a better way (place) to state this? -- Rod Taylor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] SET autocommit begins transaction?
> > What about that doesn't make sense? Having SET begin a transaction > > seems like a gross violation of POLS and likely to contradict the spec > > and cause problems with many applications. -sc > > I think we left the standard when we made SET rollbackable. Maybe that > has to be reopened because if we did that, it would make perfect sense > because all SETs would be outside transactions. Of course, the reason they're rollbackable is: begin; create schema newschema; set search_path = newschema; rollback; create table junk; -- DOH! -- Rod Taylor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #772: Rewriting on multi-record updates is
1 | 2 > 1 | 2 | 3 > 2 | 1 | 5 > 2 | 2 | 7 > (4 rows) > > SELECT * FROM T1; > id | x > + > 1 | 7 > 2 | 10 > (2 rows) > > UPDATE T2 SET X=0 WHERE ID=1 AND T1=1; > UPDATE 1 > UPDATE T2 SET X=0 WHERE ID=1 AND T1=2; > UPDATE 1 > UPDATE T2 SET X=0 WHERE ID=2 AND T1=1; > UPDATE 1 > UPDATE T2 SET X=0 WHERE ID=2 AND T1=2; > UPDATE 1 > SELECT * FROM T2; > id | t1 | x > ++--- > 1 | 1 | 0 > 1 | 2 | 0 > 2 | 1 | 0 > 2 | 2 | 0 > (4 rows) > > SELECT * FROM T1; > id | x > +--- > 1 | 0 > 2 | 0 > (2 rows) > > -- Mass update (1) > > UPDATE T2 SET X=3 WHERE ID=1; > UPDATE 2 > UPDATE T2 SET X=5 WHERE ID=2; > UPDATE 2 > SELECT * FROM T2; > id | t1 | x > ++--- > 1 | 1 | 3 > 1 | 2 | 3 > 2 | 1 | 5 > 2 | 2 | 5 > (4 rows) > > SELECT * FROM T1; > id | x > +--- > 1 | 8 > 2 | 8 > (2 rows) > > UPDATE T2 SET X=0; > UPDATE 4 > UPDATE T1 SET X=0; > UPDATE 2 > SELECT * FROM T2; > id | t1 | x > ++--- > 1 | 1 | 0 > 1 | 2 | 0 > 2 | 1 | 0 > 2 | 2 | 0 > (4 rows) > > SELECT * FROM T1; > id | x > +--- > 1 | 0 > 2 | 0 > (2 rows) > > -- Mass update (2) > > UPDATE T2 SET X=3 WHERE T1=1; > UPDATE 2 > UPDATE T2 SET X=5 WHERE T1=2; > UPDATE 2 > SELECT * FROM T2; > id | t1 | x > ++--- > 1 | 1 | 3 > 2 | 1 | 3 > 1 | 2 | 5 > 2 | 2 | 5 > (4 rows) > > SELECT * FROM T1; > id | x > +--- > 1 | 3 > 2 | 5 > (2 rows) > > UPDATE T2 SET X=0; > UPDATE 4 > UPDATE T1 SET X=0; > UPDATE 2 > SELECT * FROM T2; > id | t1 | x > ++--- > 1 | 1 | 0 > 2 | 1 | 0 > 1 | 2 | 0 > 2 | 2 | 0 > (4 rows) > > SELECT * FROM T1; > id | x > +--- > 1 | 0 > 2 | 0 > (2 rows) > > -- Mass update (3) > > UPDATE T2 SET X=7; > UPDATE 4 > SELECT * FROM T2; > id | t1 | x > ++--- > 1 | 1 | 7 > 2 | 1 | 7 > 1 | 2 | 7 > 2 | 2 | 7 > (4 rows) > > SELECT * FROM T1; > id | x > +--- > 1 | 7 > 2 | 7 > (2 rows) > > > > Sample Code > -- Version > > SELECT VERSION(); > > -- Create test tables > > CREATE TABLE T1 ( > ID integer NOT NULL Primary Key, > X integer DEFAULT 0 NOT NULL > ); > > CREATE TABLE T2 ( > ID integer NOT NULL, > T1 integer NOT NULL Constraint T2_T1 REFERENCES T1(ID), > Constraint T2_PKey Primary Key (ID, T1), > X integer DEFAULT 0 NOT NULL > ); > > CREATE RULE UpdateT2 AS > ON UPDATE TO T2 DO > (UPDATE T1 SET X = X - old.X WHERE ID = old.T1; > UPDATE T1 SET X = X + new.X WHERE ID = new.T1); > > -- Test initial values > > INSERT INTO T1(ID) VALUES(1); > INSERT INTO T1(ID) VALUES(2); > > INSERT INTO T2(ID, T1) VALUES(1, 1); > INSERT INTO T2(ID, T1) VALUES(1, 2); > INSERT INTO T2(ID, T1) VALUES(2, 1); > INSERT INTO T2(ID, T1) VALUES(2, 2); > > SELECT * FROM T2; > SELECT * FROM T1; > > -- Update one-by-one > > UPDATE T2 SET X=2 WHERE ID=1 AND T1=1; > > SELECT * FROM T2; > SELECT * FROM T1; > > UPDATE T2 SET X=3 WHERE ID=1 AND T1=2; > > SELECT * FROM T2; > SELECT * FROM T1; > > UPDATE T2 SET X=5 WHERE ID=2 AND T1=1; > > SELECT * FROM T2; > SELECT * FROM T1; > > UPDATE T2 SET X=7 WHERE ID=2 AND T1=2; > > SELECT * FROM T2; > SELECT * FROM T1; > > UPDATE T2 SET X=0 WHERE ID=1 AND T1=1; > UPDATE T2 SET X=0 WHERE ID=1 AND T1=2; > UPDATE T2 SET X=0 WHERE ID=2 AND T1=1; > UPDATE T2 SET X=0 WHERE ID=2 AND T1=2; > > SELECT * FROM T2; > SELECT * FROM T1; > > -- Mass update (1) > > UPDATE T2 SET X=3 WHERE ID=1; > UPDATE T2 SET X=5 WHERE ID=2; > > SELECT * FROM T2; > SELECT * FROM T1; > > UPDATE T2 SET X=0; > UPDATE T1 SET X=0; > > SELECT * FROM T2; > SELECT * FROM T1; > > -- Mass update (2) > > UPDATE T2 SET X=3 WHERE T1=1; > UPDATE T2 SET X=5 WHERE T1=2; > > SELECT * FROM T2; > SELECT * FROM T1; > > UPDATE T2 SET X=0; > UPDATE T1 SET X=0; > > SELECT * FROM T2; > SELECT * FROM T1; > > -- Mass update (3) > > UPDATE T2 SET X=7; > > SELECT * FROM T2; > SELECT * FROM T1; > > > No file was uploaded with this report > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Rod Taylor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] HELP
On Thu, 2002-09-12 at 01:13, Brendon Matthews wrote: > Hi, > > I have been using PostgreSQL 7.2.2 for a while now on one of our live > machines, however all of a sudden with no warning, we are no longer able to > run any UPDATE statements on one of our databases. SELECT and INSERT > statements have no issues. Try VACUUM FULL (different than regular VACUUM). Try dumping data / shutdown daemon / fry data directory / initdb / start daemon / restore data. -- Rod Taylor ---(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 #759: 7.3beta1 DROP COLUMN DEPENDENCY PROBLEM
On Fri, 2002-09-06 at 10:07, Tim Knowles wrote: > Hi Guys, > > I haven't got access to my test machine which is at home so I can't try it > but will the previous practice of creating a temp table (minus the column > you want to delete), dropping the orignal and renaming the temp table work > in 7.3 or will the dependency check catch this when you try and drop the > orignal table too? If this is the case I'd imagine the route to take would > be to manually dump all the dependent views, drop those views, drop the > column and then recreate the views. Yup.. Your going to have to drop all of the views, then the column. Or, ALTER TABLE ... DROP COLUMN .. CASCADE -- which will blow away the views for you. > On Fri, 2002-09-06 at 09:17, Tom Lane wrote: > > Rod Taylor <[EMAIL PROTECTED]> writes: > > > Indeed. At the INNER JOIN it would appear that an alias is applied to > > > the columns of a given table. > > > ... > > > The real trick is to make INNER JOINS less greedy in their requirements > > > based on the columns that are actually used. > > > > What surprised me about this report was not that the JOIN syntax exposed > > a dependency on column c, but that the non-JOIN syntax didn't. There > > shouldn't be any semantic difference AFAIR, so it seems to me that at > > least one of these behaviors needs to be fixed. > > > > I am not sure that it's practical to remove the dependency as Tim is > > hoping for... > > I wondered about that too, but by that time figured I was in way over my > head. > > The big difference is that the INNER JOIN code needs to drop one of the > colb's coming up with a virtual relation cola, colb, colc; where the > other doesn't have such a renaming scheme. > > Or, thats how it appears to function to the user. I've not dug into the > internals. > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #759: 7.3beta1 DROP COLUMN DEPENDENCY PROBLEM
On Fri, 2002-09-06 at 09:17, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > Indeed. At the INNER JOIN it would appear that an alias is applied to > > the columns of a given table. > > ... > > The real trick is to make INNER JOINS less greedy in their requirements > > based on the columns that are actually used. > > What surprised me about this report was not that the JOIN syntax exposed > a dependency on column c, but that the non-JOIN syntax didn't. There > shouldn't be any semantic difference AFAIR, so it seems to me that at > least one of these behaviors needs to be fixed. > > I am not sure that it's practical to remove the dependency as Tim is > hoping for... I wondered about that too, but by that time figured I was in way over my head. The big difference is that the INNER JOIN code needs to drop one of the colb's coming up with a virtual relation cola, colb, colc; where the other doesn't have such a renaming scheme. Or, thats how it appears to function to the user. I've not dug into the internals. ---(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 #759: 7.3beta1 DROP COLUMN DEPENDENCY PROBLEM
Indeed. At the INNER JOIN it would appear that an alias is applied to
the columns of a given table.
{ RTE :alias { ALIAS :aliasname b :colnames <>} :eref { ALIAS :aliasname
b :colnames ( "col_b" "col_c" )} :rtekind 0 :relid 17194 :inh true
:inFromCl true :checkForRead true :checkForWrite false :checkAsUser 1})
The dependency mechanism sees col_c and marks it as a requirement of
this -- which it is. Removal of col_c will break this view.
The real trick is to make INNER JOINS less greedy in their requirements
based on the columns that are actually used. This could be a large
undertaking however.
On Fri, 2002-09-06 at 04:31, [EMAIL PROTECTED] wrote:
> Tim Knowles ([EMAIL PROTECTED]) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> 7.3beta1 DROP COLUMN DEPENDENCY PROBLEM
>
> Long Description
> Hi,
>
> Been playing with the 7.3beta1 version and I've noticed a small problem with
>dependency checking when dropping a column. If you have a view which uses JOIN's to
>join tables then dropping a column will fail on a dependency check, even though the
>column being dropped is not used at all in the view. If you join the tables in the
>WHERE clause the column can be dropped without problems.
>
> Please see below some example SQL to demonstrate:
>
> Sample Code
> -- wrap it all up in a transaction so we don't do anything permanent
>
> BEGIN;
>
> CREATE TABLE table1 (col_a text, col_b int);
> CREATE TABLE table2 (col_b int, col_c text);
>
> CREATE VIEW tester1 AS SELECT A.col_a,B.col_b FROM table1 A, table2 B WHERE
>(b.col_b=a.col_b);
>
> CREATE VIEW tester2 AS SELECT A.col_a,B.col_b FROM table2 B INNER JOIN table1 A ON
>(b.col_b=a.col_b);
>
> --Now try and drop column col_c from table2
> ALTER TABLE table2 DROP COLUMN col_c RESTRICT;
>
> --You should now get an error to say that col_c is a dependent object in view tester2
>
> ROLLBACK;
>
> No file was uploaded with this report
>
>
> ---(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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug #756: suggestion: file with password instead of
> > My understanding is that it's a single password, not a list. > > It isn't a single password. It is a file containing one password per > line with pattern matching. Oh, well in that case it should probably be a default spot in the home directory. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Bug #756: suggestion: file with password instead of
On Thu, 2002-09-05 at 12:14, Bruce Momjian wrote: > Rod Taylor wrote: > > On Thu, 2002-09-05 at 05:13, Reinhard Max wrote: > > > On 4 Sep 2002 at 12:48, Rod Taylor wrote: > > > > > > > This item: > > > > Add file to hold passwords using PGPASSWORDFILE environment variable > > > > > > > > Has been completed, and will be a part of the 7.3 release. > > > > > > Is there a default file name like ~/.pgpassword so that this feature > > > can be used without setting yet another environment variable? > > > > I'm not entirely sure, but I don't believe so. You can read the 7.3 > > docs at developer.postgresql.org. > > Is there a good reason for a default for this? If we have a default, > there will be no way to disable the lookups except by renaming the file. > On the other hand, no default means that people will make up their own > names for the file, and that seems bad. My understanding is that it's a single password, not a list. As such you would probably not want a default, as each database you connect to will (should?) have a different password. By setting a default file we may encourage users to use the same password throughout all PostgreSQL databases. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #756: suggestion: file with password instead of
On Thu, 2002-09-05 at 05:13, Reinhard Max wrote: > On 4 Sep 2002 at 12:48, Rod Taylor wrote: > > > This item: > > Add file to hold passwords using PGPASSWORDFILE environment variable > > > > Has been completed, and will be a part of the 7.3 release. > > Is there a default file name like ~/.pgpassword so that this feature > can be used without setting yet another environment variable? I'm not entirely sure, but I don't believe so. You can read the 7.3 docs at developer.postgresql.org. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Bug #756: suggestion: file with password instead of
This item: Add file to hold passwords using PGPASSWORDFILE environment variable Has been completed, and will be a part of the 7.3 release. Thanks, Rod On Wed, 2002-09-04 at 11:54, [EMAIL PROTECTED] wrote: > Wojciech Scigala ([EMAIL PROTECTED]) reports a bug with a severity of 4 > The lower the number the more severe it is. > > Short Description > suggestion: file with password instead of $PGPASSWORD > > Long Description > This is not a bug-report in fact, but a suggestion of a feature. > (I couldn't find an separate email for suggestions and ideas). > As you know, many people have trouble keeping both secure (passworded) access to PG >databased while allowing some unattended access for them, for example for backing up. > Enviroment variable $PGPASSWORD is very useful here, but sensitive data should not >be passed via enviroment. The better way to do it is to provide a name of file which >contains the password. The file's access rights (if set properly) will provide >necessary security in an easy way. And that's what I'm suggesting - an introdution of >new variable, say $PGPASSWORD_FILE which will point to a file with password. > > Sample Code > > > No file was uploaded with this report > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(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 #755: Error on create a new sequence by using the
7.3 will partially fix this issue by extending the length of the NAME
data type.
Ie. You won't hit it until you have large (32+ character) length names.
On Tue, 2002-09-03 at 16:31, [EMAIL PROTECTED] wrote:
> Søren Laursen ([EMAIL PROTECTED]) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> Error on create a new sequence by using the create table command.
>
> Long Description
> In postgresql 7.1 and 7.2.1:
>
> Then using a statement like:
> CREATE TABLE longtablename(
> longtablenamenr serial NOT NULL UNIQUE PRIMARY KEY,
>
> )
>
> and later a
> CREATE TABLE longtablenamestep(
> longtablenamestepnr serial NOT NULL UNIQUE PRIMARY KEY,
>
> )
>
> I get an error on creating the last table. This is because the new sequence is named
>with the same name as the one used by the first table. The create statement is then
>terminated and no table is created. I would have to create the sequence my self and
>then create the table using statements like:
> not null default nextval('"longtablename_longtablename_sq1"'::text).
>
> Regards,
>
> Soeren Laursen
>
>
> Sample Code
>
>
> No file was uploaded with this report
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] ERROR: parser: parse error at or near "'"
Single single quote literals, double quote literals. 'profiles' => "profiles" On Tue, 2002-09-03 at 10:56, * * wrote: > I'm getting the error ERROR: parser: parse error at or near "'" > This is my sql statement in my servlet and i don't see what's wrong with it. > > SQL = "Select users.first_name, users.last_name, profiles.profile_name, > countries.city, countries.province" + > "countries.country, users.email_address FROM 'profiles' INNER > JOIN companies ON profiles.owner_id_bus. = companies.company_id" + > "INNER JOIN users ON companies.default_contact_id = > users.user_id INNER JOIN countries ON companies.location_id = countries.id" > + > "WHERE profiles.profile_name ='" + strName + "'";ERROR: parser: > parse error at or near "'" > > > > _ > Join the worlds largest e-mail service with MSN Hotmail. > http://www.hotmail.com > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(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] Command-line client sometimes misinterprets tabs for
> Can tab completion be disabled without disabling command line history? I > noticed -n disables both. I would gladly trade tab completion for tab > pastability, but I rely heavily on command line history. Perhaps annoying, but a simple work around is to use Formats like tab in the code, but space/tab combinations don't complete to anything, registering like a space in psql. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Command-line client sometimes misinterprets tabs for
The problem is that most items in postgresql are tab completed. Cut and past your text into a bash or zsh prompt. It injects a bunch of filenames into your data, as per tab completion. Annoying, but for the benefits it's livable. Perhaps a toggle could be created to disable tab completion? On Mon, 2002-08-26 at 23:36, Casey Allen Shobe wrote: > Let's say I have typed a nice formatted SQL statement like so: > > selectb1.blah, > b1.blah2, > b2.blah3 > from blah_table as b1 > inner join only blah_table2 as b2 > onb1.blah = b2.blah; > > ...using tabs as the indenting medium. Now I copy that from my editor into > psql, and most of the tabs are converted to nulls, resulting in an error like > "Unknown value fromblah_table". Tabs should paste; they're normal > characters. > > -- > Casey Allen Shobe / Network Security Analyst & PHP Developer > SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144 > [EMAIL PROTECTED] / http://www.secureworks.net > Content is my own and does not necessarily represent my company. > > "Diagoras the Atheist once visited Samothrace and a friend there said to him, > 'You think the gods have no care for humans? Why, you can see from all these > votive pictures here how many people have escaped the fury of storms at sea > by praying to the gods, who have brought them safe to harbour.' 'Yes, > indeed,' said Diagoras, 'but where are the pictures of all those who suffered > shipwreck and perished in the waves?' On another occasion he was on a voyage > and the crew became anxious about the bad weather and began to mutter that it > served them right for taking an atheist on board. Diagoras just pointed out > to them a number of other ships on the same course which were in equal > difficulties and asked them whether they thought that there was a Diagoras on > the passenger-list of every one of them. The fact is that a man's character > or way of life makes no difference at all to his good luck or bad." > > - Cicero > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] Foreign Key woes -- 7.2 and ~7.3
b=# create table stuff (stuff_id serial unique); NOTICE: CREATE TABLE will create implicit sequence 'stuff_stuff_id_seq' for SERIAL column 'stuff.stuff_id' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'stuff_stuff_id_key' for table 'stuff' CREATE b=# create table stuff2 (stuff_id int4 references stuff on update cascade on delete cascade); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: PRIMARY KEY for referenced table "stuff" not found You'll notice there isn't a primary key at all -- which shouldn't be an issue as there is still the unique. Not the brightest thing to do, but surely the primary key shouldn't be enforced to exist before a plain old unique. If thats the case, then unique indecies need to be blocked until there is a primary key, or the first one should be automatically marked as the primary key. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] 7.2 crash...
7.2 crashes with the below function: CREATE OR REPLACE FUNCTION runMaintenance() RETURNS BOOL AS ' VACUUM; SELECT TRUE; ' LANGUAGE sql; I was going to toss a bunch of system maintenance stuff in a database function to make administration for those who administer the boxes (not me -- I just tell how). Anyway, any crash is a bad crash I suppose. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
