Re: [SQL] rows equal
On 3/20/01, 7:23:04 PM, Marios Moutzouris <[EMAIL PROTECTED]> wrote regarding [SQL] rows equal: > Hello > I need a sql query to a postgresql database which can tell me whether the > rows returned ar equal. > i.e > --- > Status > --- > Order > --- > Collected > -- > This is two columns. Both are not equal. > Any help?...without having to write code to fetch the result and go > through etc etc. Sorry - don't think so. Unless you've defined a unique index on one or more of these columns you'll have to check the contents of the columns. If they are never supposed to be the same, define a unique index. - Richard Huxton ---(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: [SQL] Serials.
Thus spake Grant > Please see below for my table schema. I have two questions. > > (1) Why is a sequence limited to 2147483647, it seems very small? Yikes! What are you counting? :-) The value 2147483647 is the largest value that can fit into an int. It is equal to 0x7fff in hex. If you add one to that you get -2147483648. I suppose the number could be doubled by using an unsigned int for the serial type. It should never be negative anyway. > (2) If I reset the sequence, then try another insert. It will not insert > anything until it cycles through all sequences and finds an unused one. It > will give the following error each time it tries to insert a row with a > used sequence: > > PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique > index releases_pkey > > How can I possibly get around this issue so that I can be sure to always > have a free id without getting the error above? Well, there are a lot of questions to ask before answering that one. What is the reason for resetting the sequence? Do you actually have values in that range and are hitting a ceiling. Are there a lot of holes in your sequence? The short ansqwer is to use max and setval to reset your index above the highest number but if holes are the problem then that won't help. You may need to run a script that renumbers down but don't forget to renumber any tables that reference it. Referential integrity constraints may also get in your way. Hey, this is PostgreSQL. If you don't like the way that nextval works then just create your own function with the behaviour that you need. You can keep your own list of numbers and fill holes and all sorts of things. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Serials.
Grant wrote: > > Please see below for my table schema. I have two questions. > > (1) Why is a sequence limited to 2147483647, it seems very small? That's 2 billion(ish) - the largest signed 32 bit integer. > (2) If I reset the sequence, then try another insert. It will not insert > anything until it cycles through all sequences and finds an unused one. It > will give the following error each time it tries to insert a row with a > used sequence: > > PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique > index releases_pkey > > How can I possibly get around this issue so that I can be sure to always > have a free id without getting the error above? Well - you've told PG to only allow unique id values and then reset the sequence that generates its values. It's doing pretty much what you'd expect, I'd say. How fast are you inserting these bookings? According to my calculations that's a sustained rate of 68 inserts/sec over a whole year. If you just want unique records, put the primary key over id,added and let the id_seq cycle. If you want more than 2 billion unique id values you'll need to combine the sequence with another value - see my postgresql notes at techdocs.postgresql.org and perhaps look into the plpgsql cookbook (www.brasileiro.net from memory) - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] how do I check if a temporary table exists?
Thus spake datactrl > How do I check if a temporary table exists? Searching pg_tables with a > temporary table name we chose always fails. What do you mean? You know that it exists because you just created it and the create succeeded. Can you describe a situation where you need to know about a temp table and don't already know? It can't go into the pg_tables table because then it would be visible to other processes. Are you perhaps misunderstanding temp tables? You cannot see them except from the process that created them. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Serials.
On Sat, Mar 24, 2001 at 03:55:09PM +1000, Grant allegedly wrote:
> Please see below for my table schema. I have two questions.
>
> (1) Why is a sequence limited to 2147483647, it seems very small?
This is the maximum value a signed integer (32-bit) can contain. If
this really is a problem for you, then it might be a good idea to
look into another database; one specifically suited for huge databases,
such as NCR Terabase.
To put things in perspective: if you have a table with more than two
billion records each about 50 bytes in size you would have more than
(!) 100 GB of data... I don't think PostgreSQL is meant for those
enormous amounts of data, although it may still work porperly. Most
larger commercial database offerings (Oracle and Sybase come to mind)
will still work well, but once you're database contains a few terabytes
of data you're just asking for problems. There is a very good chance
that you can spread the data accross several database, increasing
performance and maintainability.
Of course, writing your own serial implementation is not that difficult.
If you use 64-bit integers (int8), you should have plenty of space :)
Search the mailinglist archive for some clues, since there was a discussion
about this (implementing a sequence that doesn't have holes) one or two
days ago.
> (2) If I reset the sequence, then try another insert. It will not insert
> anything until it cycles through all sequences and finds an unused one. It
> will give the following error each time it tries to insert a row with a
> used sequence:
>
> PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique
> index releases_pkey
>
> How can I possibly get around this issue so that I can be sure to always
> have a free id without getting the error above?
Get the maximum primary key value from the table and start the sequence
from the number following it.
I hope this helps,
Mathijs
--
$_='while(read+STDIN,$_,2048){$a=29;$c=142;if((@a=unx"C*",$_)[20]&48){$h=5;
$_=unxb24,join"",@b=map{xB8,unxb8,chr($_^$a[--$h+84])}@ARGV;s/...$/1$&/;$d=
unxV,xb25,$_;$b=73;$e=256|(ord$b[4])<<9|ord$b[3];$d=$d>>8^($f=($t=255)&($d
>>12^$d>>4^$d^$d/8))<<17,$e=$e>>8^($t&($g=($q=$e>>14&7^$e)^$q*8^$q<<6))<<9
,$_=(map{$_%16or$t^=$c^=($m=(11,10,116,100,11,122,20,100)[$_/16%8])&110;$t
^=(72,@z=(64,72,$a^=12*($_%16-2?0:$m&17)),$b^=$_%64?12:0,@z)[$_%8]}(16..271))
[$_]^(($h>>=8)+=$f+(~$g&$t))for@a[128..$#a]}print+x"C*",@a}';s/x/pack+/g;eval
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Serials.
Grant writes: > (1) Why is a sequence limited to 2147483647, it seems very small? Because that's what a four-byte signed integer takes. No one has stepped forward to implement 8-byte sequence counters, yet. > (2) If I reset the sequence, then try another insert. It will not insert > anything until it cycles through all sequences and finds an unused one. It > will give the following error each time it tries to insert a row with a > used sequence: > > PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique > index releases_pkey Why did you reset the sequence in the first place? You should probably set it back to where it was (using setval()). Sequences simply return incrementing numbers, they don't fill holes or have any constraint-avoiding logic. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] all views in database broken at once
Greetings- I'm in a bit of a pickle. I rebuilt a big query on top of which lots of little queries rest, so as to use some new columns in the query. Now, I get error messages when trying to access any view that SELECTs from the rebuilt query: fgdata=# \d sx_l_m_r_a ERROR: cache lookup of attribute 197 in relation 47074 failed fgdata=# select * from pg_views; ERROR: cache lookup of attribute 317 in relation 48494 failed A SELECT from the rebuilt query itself works fine, so I know it's not actually a data problem. Is there anything I can do to rebuild these views? I don't think I have the original SQL sitting around to drop and recreate them. Thanks for any advice. -- Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology (Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill) [EMAIL PROTECTED] - http://www.unc.edu/~aperrin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how do I check if a temporary table exists?
[EMAIL PROTECTED] (D'Arcy J.M. Cain) writes: > Thus spake datactrl >> How do I check if a temporary table exists? Searching pg_tables with a >> temporary table name we chose always fails. > What do you mean? You know that it exists because you just created it > and the create succeeded. Can you describe a situation where you need > to know about a temp table and don't already know? I can see that it might take some unpleasant restructuring of his client application to make that knowledge available where & when it's needed. > It can't go into the pg_tables table because then it would be visible > to other processes. Are you perhaps misunderstanding temp tables? You > cannot see them except from the process that created them. I agree with datactrl that this is a deficiency: the whole point of system catalogs is that you are supposed to be able to look in them to see what you have. It should be possible to look up temp tables in pg_class. They actually are in there (and quite visible to other processes BTW); what you don't know is the mapping from your logical name for the table to its real name pg_temp. I'm hoping that this can be fixed when we implement schemas. Seems like it would be natural to make temp tables live in a per-backend schema. Then they wouldn't need names like pg_temp. You would probably need to know the name of your current temp schema, but that seems like a more tractable thing to deal with than mappings of individual temp table names. regards, tom lane ---(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: [SQL] how do I check if a temporary table exists?
Multiple backends can create temp tables with the same name and each person will see their own version. > Thus spake datactrl > > How do I check if a temporary table exists? Searching pg_tables with a > > temporary table name we chose always fails. > > What do you mean? You know that it exists because you just created it > and the create succeeded. Can you describe a situation where you need > to know about a temp table and don't already know? > > It can't go into the pg_tables table because then it would be visible > to other processes. Are you perhaps misunderstanding temp tables? You > cannot see them except from the process that created them. > > -- > D'Arcy J.M. Cain| Democracy is three wolves > http://www.druid.net/darcy/| and a sheep voting on > +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] all views in database broken at once
Andrew Perrin <[EMAIL PROTECTED]> writes:
> fgdata=# \d sx_l_m_r_a
> ERROR: cache lookup of attribute 197 in relation 47074 failed
> fgdata=# select * from pg_views;
> ERROR: cache lookup of attribute 317 in relation 48494 failed
> A SELECT from the rebuilt query itself works fine, so I know it's not
> actually a data problem. Is there anything I can do to rebuild these
> views? I don't think I have the original SQL sitting around to drop and
> recreate them.
You're in deep trouble :-(.
It's at least theoretically possible to fix this by hand, but it'll be
tedious. You'll need to dump out the "compiled" form of the view rule
for each broken view, manually correct the OID for each referenced view,
and UPDATE pg_rewrite with the corrected rule string.
A quick example:
regression=# create view vv1 as select * from int8_tbl;
CREATE
regression=# select ev_action from pg_rewrite where rulename = '_RETvv1';
({ QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal false :isBinary
false :isTemp false :hasAggs false :hasSubLinks false :rtable ({ RTE :relname vv1
:relid 147764 :subquery <> :alias { ATTR :relname *OLD* :attrs <>} :eref { ATTR
:relname *OLD* :attrs ( "q1" "q2" )} :inh false :inFromCl false :checkForRead false
:checkForWrite false :checkAsUser 0} { RTE :relname vv1 :relid 147764 :subquery <>
:alias { ATTR :relname *NEW* :attrs <>} :eref { ATTR :relname *NEW* :attrs ( "q1"
"q2" )} :inh false :inFromCl false :checkForRead false :checkForWrite false
:checkAsUser 0} { RTE :relname int8_tbl :relid 18887 :subquery <> :alias <> :eref {
ATTR :relname int8_tbl :attrs ( "q1" "q2" )} :inh true :inFromCl true :checkForRead
true :checkForWrite false :checkAsUser 256}) :jointree { FROMEXPR :fromlist ({
RANGETBLREF 3 }) :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM
:resno 1 :restype 20 :restypmod -1 :resname q1 :reskey 0 :reskeyop 0 :resso!
rtgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 1 :vartype 20 :vartypmod
-1 :varlevelsup 0 :varnoold 3 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2
:restype 20 :restypmod -1 :resname q2 :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 3 :varattno 2 :vartype 20 :vartypmod -1
:varlevelsup 0 :varnoold 3 :varoattno 2}}) :groupClause <> :havingQual <>
:distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()})
What you need to fix are the :relid fields of the RTE entries for the
referenced tables. The :relname field of the RTE gives the real name
of the table it references, and you look in pg_class for the associated
OID. For example,
regression=# select oid from pg_class where relname = 'int8_tbl';
oid
---
18887
(1 row)
shows that the above view's reference to int8_tbl isn't broken.
Of course you'll need to be superuser to do the UPDATE on pg_rewrite,
and you will probably find that you need to quit and restart the backend
before it will use the changed view definition.
Good luck!
regards, tom lane
PS: Yes, I know we gotta fix this...
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] Serials.
> > (1) Why is a sequence limited to 2147483647, it seems very small? > > Yikes! What are you counting? :-) I have a message board. Where users can send each other messages. I doubt I will ever get 2147483647 messages, but I want to make sure I never get an error where the message isn't sent. I guess the best option is to create a function that changes all the ids to the lowest values possible and then set the sequence to the maximum value + 1. Thanks everyone for your help. ---(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: [SQL] all views in database broken at once
Thanks - I appreciate the quick reply. As it turns out, I was able to find
the original SQL I used to generate (most of) the queries, so I'm
okay. But I'm intrigued: what is it that causes this? Is it *my*
recreating the view on which the other views depend, or is it some
internal glitch?
Thanks again.
--
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
[EMAIL PROTECTED] - http://www.unc.edu/~aperrin
On Sat, 24 Mar 2001, Tom Lane wrote:
> Andrew Perrin <[EMAIL PROTECTED]> writes:
> > fgdata=# \d sx_l_m_r_a
> > ERROR: cache lookup of attribute 197 in relation 47074 failed
> > fgdata=# select * from pg_views;
> > ERROR: cache lookup of attribute 317 in relation 48494 failed
>
> > A SELECT from the rebuilt query itself works fine, so I know it's not
> > actually a data problem. Is there anything I can do to rebuild these
> > views? I don't think I have the original SQL sitting around to drop and
> > recreate them.
>
> You're in deep trouble :-(.
>
> It's at least theoretically possible to fix this by hand, but it'll be
> tedious. You'll need to dump out the "compiled" form of the view rule
> for each broken view, manually correct the OID for each referenced view,
> and UPDATE pg_rewrite with the corrected rule string.
>
> A quick example:
>
> regression=# create view vv1 as select * from int8_tbl;
> CREATE
> regression=# select ev_action from pg_rewrite where rulename = '_RETvv1';
>
> ({ QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal false
>:isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable ({ RTE
>:relname vv1 :relid 147764 :subquery <> :alias { ATTR :relname *OLD* :attrs <>}
>:eref { ATTR :relname *OLD* :attrs ( "q1" "q2" )} :inh false :inFromCl false
>:checkForRead false :checkForWrite false :checkAsUser 0} { RTE :relname vv1 :relid
>147764 :subquery <> :alias { ATTR :relname *NEW* :attrs <>} :eref { ATTR :relname
>*NEW* :attrs ( "q1" "q2" )} :inh false :inFromCl false :checkForRead false
>:checkForWrite false :checkAsUser 0} { RTE :relname int8_tbl :relid 18887 :subquery
><> :alias <> :eref { ATTR :relname int8_tbl :attrs ( "q1" "q2" )} :inh true
>:inFromCl true :checkForRead true :checkForWrite false :checkAsUser 256}) :jointree {
>FROMEXPR :fromlist ({ RANGETBLREF 3 }) :quals <>} :rowMarks () :targetList ({
>TARGETENTRY :resdom { RESDOM :resno 1 :restype 20 :restypmod -1 :resname q1 :reskey 0
>:reskeyop 0 :res!
so!
> rtgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 1 :vartype 20
>:vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 1}} { TARGETENTRY :resdom {
>RESDOM :resno 2 :restype 20 :restypmod -1 :resname q2 :reskey 0 :reskeyop 0
>:ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 2 :vartype 20
>:vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 2}}) :groupClause <> :havingQual
><> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
>:resultRelations ()})
>
> What you need to fix are the :relid fields of the RTE entries for the
> referenced tables. The :relname field of the RTE gives the real name
> of the table it references, and you look in pg_class for the associated
> OID. For example,
>
> regression=# select oid from pg_class where relname = 'int8_tbl';
> oid
> ---
> 18887
> (1 row)
>
> shows that the above view's reference to int8_tbl isn't broken.
>
> Of course you'll need to be superuser to do the UPDATE on pg_rewrite,
> and you will probably find that you need to quit and restart the backend
> before it will use the changed view definition.
>
> Good luck!
>
> regards, tom lane
>
> PS: Yes, I know we gotta fix this...
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>
---(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: [SQL] all views in database broken at once
Andrew Perrin <[EMAIL PROTECTED]> writes: > But I'm intrigued: what is it that causes this? Is it *my* > recreating the view on which the other views depend, Yes. You dropped and recreated the view --- the new version may have the same name but it's not the same OID, so it isn't the same object. And the other views refer to it by OID. The ultimate solution should have two parts, IMHO: 1. Dependency checking so that you *can't* drop a view that is still referenced. However this will not be complete --- it's not clear that we can detect references inside PL functions, for example. 2. An ALTER VIEW command that lets you change a view's defining query, while keeping the same OID, as long as the names and types of the output columns don't change. This would reduce the need to drop and recreate views. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] all views in database broken at once
On Sat, Mar 24, 2001 at 07:50:00PM -0500, Tom Lane allegedly wrote: > Andrew Perrin <[EMAIL PROTECTED]> writes: > > But I'm intrigued: what is it that causes this? Is it *my* > > recreating the view on which the other views depend, > > Yes. You dropped and recreated the view --- the new version may have > the same name but it's not the same OID, so it isn't the same object. > And the other views refer to it by OID. > > The ultimate solution should have two parts, IMHO: > > 1. Dependency checking so that you *can't* drop a view that is still > referenced. However this will not be complete --- it's not clear that > we can detect references inside PL functions, for example. > > 2. An ALTER VIEW command that lets you change a view's defining query, > while keeping the same OID, as long as the names and types of the output > columns don't change. This would reduce the need to drop and recreate > views. How about being able to recompile them (keeping the SQL around in the system catalogs)? Doesn't Oracle allow you to do something like that? Mathijs -- "It is a great thing to start life with a small number of really good books which are your very own". Sir Arthur Conan Doyle (1859-1930) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Serials.
Grant writes: > I have a message board. Where users can send each other messages. I > doubt I will ever get 2147483647 messages, but I want to make sure I > never get an error where the message isn't sent. Think about loads. If your users are going to be posting 10 messages/second, that's 864000 messages per day, you won't wrap for nearly 7 years. I've got a pretty heavy mail load, including spam I probably get 300 messages/day, weekends are lighter, so if you've got a bunch of weenies who are subscribed to a gazillion mailing lists you're talking three thousand users for six and a half years. A little light if you're planning on being the next Hotmail (A test account set up there gets 70 spams/day without my ever publishing the address), but for your average mid-range discussion forum you're probably good for a while. I doubt that, say, Salon's TableTalk forum gets even 10k new messages per day. > I guess the best option is to create a function that changes all the > ids to the lowest values possible and then set the sequence to the > maximum value + 1. Better idea, if one of your deletion criteria is going to be age, would just be to wrap the IDs. Dan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] all views in database broken at once
Mathijs Brands <[EMAIL PROTECTED]> writes: > How about being able to recompile them (keeping the SQL around in the > system catalogs)? Doesn't Oracle allow you to do something like that? That's another possibility. It's not real clear that there's any advantage to storing rules in preparsed form to begin with --- if we just stored the original text and reparsed it each time it was read, the system would be vastly more flexible, and probably not noticeably slower. Right now the parser doesn't really support this: there's no way to extract the portion of the original query string that corresponds to the body of the CREATE RULE/VIEW command. But that could be fixed with some straightforward hacking... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
