Re: [BUGS] select where id=random()*something returns two results
Sorry for buggering you, I get the point :) And thanks for the order by limit 1 hint. That will do. Ulrich Meis ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] select where id=random()*something returns two results
> Sorry for buggering you, I get the point :) I think you mean 'bugging.' Buggering is something quite different. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] The comment sentence of Primary-Key is lost.
Hi all.
My comment sentence of Primary-Key is lost.!
See this sample below.
---
CREATE TABLE "MyTransaction"
(
"XID" int4 NOT NULL,
"LastSeqId" int4 NOT NULL,
"HostId" int4 NOT NULL,
CONSTRAINT "MyTransaction_pkey" PRIMARY KEY ("XID", "HostId")
) WITH OIDS;
COMMENT ON CONSTRAINT "MyTransaction_pkey" ON "MyTransaction" IS 'What happens to
this?';
saito=# SELECT cls.oid, cls.relname as idxname, indrelid, indkey, indisclustered,
indisunique,
indisprimary, n.nspname, indnatts, tab.relname as tabname, indclass,
description,
pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable,
condeferred, amname
FROM pg_index idx
JOIN pg_class cls ON cls.oid=indexrelid
JOIN pg_class tab ON tab.oid=indrelid
JOIN pg_namespace n ON n.oid=tab.relnamespace
JOIN pg_am am ON am.oid=cls.relam
LEFT OUTER JOIN pg_description des ON (des.objoid=cls.oid AND des.objsubid = 0)
LEFT OUTER JOIN pg_constraint con ON con.conrelid=indrelid AND conname=cls.relname
WHERE cls.relname = 'MyTransaction_pkey';
oid | idxname | indrelid | indkey | indisclustered | indisunique |
indisprimary | nspname | indnatts |tabname
| indclass | description | indconstraint | contype | condeferrable | condeferred |
amname
++--+++-+--+-+--+---
+---+-+---+-+---+-+
518874 | MyTransaction_pkey | 518872 | 1 3| f | t | t
| saito |2 |
MyTransaction | 1978 1978 | | | p | f | f
| btree
(1 row)
*Why?*
saito=# SELECT * FROM pg_description WHERE objoid >= 518874::OID;
objoid | classoid | objsubid | description
+--+--+---
518875 |16386 |0 | What happens to this?
(1 row)
Hmm... 518875?
Do I have misunderstanding?
However,
pg_dumpall that all is supposed to be extracted after this result loses this.
As for this cause, something isn't known.
Any comment?
Regards,
Hiroshi Saito
---(end of broadcast)---
TIP 8: explain analyze is your friend
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))); JLL Rod Taylor wrote: > > > 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; > > >Name: signature.asc >signature.asc Type: application/pgp-signature > Description: This is a digitally signed message part ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] select where id=random()*something returns two results
POSTGRESQL BUG REPORT TEMPLATE Your name : Ulrich Meis Your email address : u.meis ( at ) gmx ( dot ) de System Configuration - Architecture (example: Intel Pentium) : AMD XP 1ghz Operating System (example: Linux 2.0.26 ELF) : Linux (6month old) Gentoo PostgreSQL version (example: PostgreSQL-7.3): PostgreSQL 7.3.4 Compiler used (example: gcc 2.95.2) : gnu gcc 3.2.2 Please enter a FULL description of your problem: A "select * from table where primkey=..." sometimes returns two results. Having a table of quotes created as can be seen in the next section, I've seen the following in psql: 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. This should be impossible as well, because I filled the table up without touching the serial and without deleting a single row. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: -- create table quotes (id serial,quote text,author text); Fill in some quotes... select * from quotes where id=1+round(random()* cast ((select max(id) from quotes) as double precision)); If you know how this problem might be fixed, list the solution below: - No idea. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Can't access table to describe, drop, or select, but it does exist
Howdy Folks, I was dumping a database to test backups of the tsearch2 objects, and as I glanced through the output of pg_dump -Ft database > DBdata.bak, I found a table that I hadn't seen before in the table. It's a table that's used in other databases, but not this one. Somehow it had gotten created and populated with 40,000 or so rows of data. No problem, I figured I'd drop it, and that's where things started getting bizarre. The reason I'd never noticed the table is because in doing a \d it doesn't show up in the table list. If I try to do a \d TABLE_NAME, I can use to autocomplete the name, but then it says the table doesn't exist. I can't select any of those 40,000 rows while I'm in the database, and I can't drop it, either. The only evidence of the table I can find while I'm actually in the database is by doing a select * from pg_tables, and it shows up as the following: schemaname | tablename | tableowner | hasindexes | hasrules | hastriggers +-+++--+- public | ROOT_U_QUICK_LOOKUP| cp | f | f| f Any \d on the table gives: Did not find any relation named "ROOT_U_QUICK_LOOKUP". and any select/drop on the table gives: ERROR: Relation "root_u_quick_lookup" does not exist So what's the deal? If the pg_dump wasn't giving me so much data I'd be tempted to just delete the row from pg_tables, but the rows are there, and I want to clobber them. Any ideas? Thanks, Josh Eno ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Can't access table to describe, drop, or select, but it
On Thu, 18 Sep 2003, Josh Eno wrote: > The reason I'd never noticed the table is because in doing a \d it > doesn't show up in the table list. If I try to do a \d TABLE_NAME, I > can use to autocomplete the name, but then it says the table > doesn't exist. I can't select any of those 40,000 rows while I'm in the > database, and I can't drop it, either. The only evidence of the table I > can find while I'm actually in the database is by doing a select * from > pg_tables, and it shows up as the following: You need to say things like: \d "ROOT_U_QUICK_LOOKUP" or select * from "ROOT_U_QUICK_LOOKUP" (note the double quotes to prevent casefolding). It's interesting that \d FOO gives an error message referencing "FOO" since it's actually looking for a table that's been case-folded. I think the error message is incorrect. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] create temporary sequence and ecpg
POSTGRESQL BUG REPORT TEMPLATE
Your name : Edmund Bacon
Your email address : [EMAIL PROTECTED]
System Configuration
-
Architecture (example: Intel Pentium) : Intel Pentium
Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.20
PostgreSQL version (example: PostgreSQL-7.3.4): PostgreSQL-7.3.4
Compiler used (example: gcc 2.95.2) : gcc 3.2.2
Please enter a FULL description of your problem:
Trying to create a TEMPORARY SEQUENCE with ecpg produces
a runtime erorr. the sqlprint error is:
sql error 'ERROR: parser: parse error at or near "foo" at character 27'
in lin
A workaround is to use EXECUTE IMMEDIATE
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
--
exec sql include sqlca;
$ cat tmpseq.pgc
int main()
{
exec sql whenever sqlerror sqlprint;
exec sql connect to test;
exec sql create temporary sequence foo;
exec sql disconnect;
return 0;
}
Looking at the emitted C code we see:
{ ECPGdo(__LINE__, NULL, "create sequence temporary foo ", ECPGt_EOIT,
ECPGt_EORT);
Note that the tokens "sequence" and "temporary" have been reversed.
If you know how this problem might be fixed, list the solution below:
-
--
Edmund Bacon <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] The comment sentence of Primary-Key is lost.
"Hiroshi Saito" <[EMAIL PROTECTED]> writes: > My comment sentence of Primary-Key is lost.! Yeah, pg_dump missed comments attached to primary key constraints. Fixed for 7.4. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
