Re: [SQL] Appropriate indices to create for these queries
From: "Gerald Gutierrez" <[EMAIL PROTECTED]> > > I've been looking into indices and which ones to create and I'm getting > myself a little confused. The "PostgreSQL Introduction and Concepts" book > didn't help very much. I wonder if a kind soul can give me some tips. > > SELECT * FROM T1 WHERE a=1 and b='hello'; > > Is the appropriate index for this query: > > CREATE INDEX ndx ON T1 (a, b) ? Maybe - you seem to have got to the core of the matter below... > When I was testing, it seemed that even if I created the index with only > "a", EXPLAIN told me that it would just do an index scan, seemingly > indicating that it didn't matter whether I had an "a" index, or an "a, b" > index. For the above query, any of : index on "a" , "b", "a,b" will probably be used (if you have enough data to justify it). > How about for WHERE a=1 or b='hello' and other more complex forms? Is there > documentation that describes a variety of different queries and what kind of > indices are best? In this case, an index on "a,b" isn't much use since the b='hello' values are presumably scattered amongst all the various 'a' values. In practice, unless you do a lot of a=1 and b="hello" queries you're probably better off with separate indexes on a and b, or possibly even just on one of them. I tend to apply indexes to fields that take part in a join then add them one at a time to other fields as it becomes clear which takes part in important queries. Don't forget that it takes a certain amount of effort to maintain an index. You've already found the EXPLAIN command - this is your best guide to where an index can be useful. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] passing null parameter to plpgsq functions
From: "Picard, Cyril" <[EMAIL PROTECTED]> > Hello > I wrote a plpgsql function with few parameters ; when I call the function, > some of the parameters can be null. > In this case, all the parameters are considered as null in the function's > body ! is it a feature ? how can I work around this ? It's a feature in versions before 7.1 (or possibly 7.0.x) you can still get that behaviour in 7.1 by asking for strict null handling on a specific function. The only remedy AFAIK is to upgrade. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [SQL] passing null parameter to plpgsq functions
Thank you ! I planned to use the 7.1 to get the outer join capability. Is the 7.1 stable ? > -Message d'origine- > De: Richard Huxton [SMTP:[EMAIL PROTECTED]] > Date: mardi 3 avril 2001 10:06 > À:Picard, Cyril; [EMAIL PROTECTED] > Objet:Re: [SQL] passing null parameter to plpgsq functions > > From: "Picard, Cyril" <[EMAIL PROTECTED]> > > > Hello > > I wrote a plpgsql function with few parameters ; when I call the > function, > > some of the parameters can be null. > > In this case, all the parameters are considered as null in the > function's > > body ! is it a feature ? how can I work around this ? > > It's a feature in versions before 7.1 (or possibly 7.0.x) you can still > get > that behaviour in 7.1 by asking for strict null handling on a specific > function. > > The only remedy AFAIK is to upgrade. > > - Richard Huxton > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] passing null parameter to plpgsq functions
From: "Picard, Cyril" <[EMAIL PROTECTED]> > Thank you ! I planned to use the 7.1 to get the outer join capability. > > Is the 7.1 stable ? Pretty much - it's reached release candidate stage and I've been using it for development for a while now. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] serial type; race conditions
Micheal,
Thanks, I was thinking that something like this should work.
However, I am having a problem with it. here is what I am doing.
begin;
INSERT INTO table (serial_col) (SELECT nextval('seq_serial_col'));
commit;
first I needed to add parens around the select statement. The
results are not what I expected. If I executed this a few times, when I
looked at the table what I saw was:
serial_col | seq_serial_col
1 | 2
3 | 4
5 | 6
etc.
I had thought I would do the insert, grab the currval of transaction
passing it back to my app. commit, then do an update. I can not
seem to get the seq to work.
Ted
-Original Message-
From: Michael Fork <[EMAIL PROTECTED]>
To: postgresql <[EMAIL PROTECTED]>
Date: Thu, 29 Mar 2001 10:04:46 -0500 (EST)
Subject: Re: [SQL] serial type; race conditions
> If you are looking to have every number accounted for, something
like
> this
> will work:
>
> INSERT INTO table (serial_col) SELECT nextval('seq_serial_col');
>
> UPDATE table SET foo = 'bar' , ... WHERE serial_col = (SELECT
> currval('seq_serial_col'));
>
> then, if the update fails, the number will be accounted for in the
> table (Note that you could not use not null on any of the columns).
>
> Michael Fork - CCNA - MCP - A+
> Network Support - Toledo Internet Access - Toledo Ohio
>
> On Thu, 29 Mar 2001, postgresql wrote:
>
> > How does currval work if you are not inside a transaction. I have
> > been experimenting with inserting into a table that has a
sequence.
> > If the insert fails (not using a transaction) because of bad client
> input
> > then the next insert gets the proper next number in the
sequence.
> >
> > given sequence 1,2,3,4,5 exists
> > insert into table date 1/111/01 (obviously wrong) insert fails...
> > try again with good data, insert succeeds and gets number 6 in
the
> > sequence.
> >
> > i'm getting what I want. A sequence number that does not
increment
> > on a failed insert. However, how do I get the assigned sequence
> > number with currval when I am not using a transaction? What
> > happens when multiple users are inserting at the same time?
> >
> > I am trying to create a sequence with out any "missing" numbers.
If
> > there is a failure to insert, and a sequence number is "taken". I
> want
> > the empty row.
> >
> > Thanks, it is getting clearer
> >
> > Ted
> >
> >
> > -Original Message-
> > From: Bruce Momjian <[EMAIL PROTECTED]>
> > To: [EMAIL PROTECTED]
> > Date: Mon, 26 Mar 2001 16:47:37 -0500 (EST)
> > Subject: Re: [SQL] serial type; race conditions
> >
> > > > Hi,
> > > >
> > > > I'm using serial fields to generate IDs for almost all object in
> my
> > > > database. I insert an empty row, get the CURRVAL() of the
> > sequence
> > > > and then update to that value.
> > > >
> > > > I had understood (and now, I can't find the reference to back
> this
> > > up)
> > > > that serial is implemented in such a way that race conditions
> > between
> > > > DB connections can't happen.
> > > >
> > > > Is this true?
> > >
> > > Safe. See FAQ item. currval is for your backend only.
> > >
> > > --
> > > 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 2: you can get off all lists at once with the unregister
> command
> > > (send "unregister YourEmailAddressHere" to
> > > [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
> >
>
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] pg_dumpall and password access
I'm trying to run pg_dumpall to backup all my users' bases but since I have configure pg_hba.conf to "passwd" pg_dumpall always fails: su-2.03$ pg_dumpall >backup.dat # I'm the postgres unix user. Password: psql: Password authentication failed for user 'postgres' Password: psql: Password authentication failed for user 'postgres' Password: psql: Password authentication failed for user 'postgres' # I type my password on the next line Password: Password: psql: Password authentication failed for user 'postgres' Connection to database 'sympa' failed. fe_sendauth: no password supplied pg_dump failed on sympa, exiting I need to have a password identification since I don't want the "common" users to be able to connect to any database. Beside I cannot leave the "local" to the "trust" auth scheme because every users are able to run programs on the machine hosting the database. Is there any configuration tips or workaround known for my problem ? -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pg_dumpall and password access
Christophe Labouisse <[EMAIL PROTECTED]> writes: > I'm trying to run pg_dumpall to backup all my users' bases but since I > have configure pg_hba.conf to "passwd" pg_dumpall always fails: pg_dumpall doesn't work very well with password authentication (and even if it did, storing the password in a cron script doesn't seem like a good idea to me). As long as the dumper will run on the same machine as the database server, consider using IDENT authorization instead. You'll need an identd daemon running, and you'll need to use a TCP connection because IDENT only works with TCP connections. The simplest form of pg_hba.conf entry is host all 127.0.0.1 255.255.255.255 ident sameuser If you have no other users you want to let in, you could use an explicit ident map instead of 'sameuser' to tighten this up even more. See the admin documentation for more info. I don't recommend using IDENT for connections from untrusted machines, but on localhost it's as trustworthy as your local sysadmin... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Implicit/Explicit casting of the unknown type
Hi, Apologies for re-posting - this problem is giving me a bit of a headache. Basically, my problem is that in a number of (to me surprising) contexts an explicit cast from a string literal to some character data type is required by PostgreSQL, where such casts are not required by other rdbms with which I am more familiar. (Examples below.) Is there a rule of thumb I can use to tell when such a cast is required? I hope someone can help. Regards, Stephen Jackson Home: [EMAIL PROTECTED] www.panting-deer.org.uk Work: [EMAIL PROTECTED]www.looksystems.co.uk On Mon, 26 Mar 2001 21:03:55 +0100, Stephen Jackson wrote: > > Hi, > > I am new to this list. I have browsed the list archives and tried a > search, but haven't found anything about my problem. > > I am porting several applications to PostgreSQL and have come across a > number of instances where a string literal does not work without an > explicit cast to some character based datatype. Two examples are given > below: one where the string literal is in the column list, and one where > it is in a WHERE clause. > > Are there bugs in the area of determining a type for the unknown type? > Or is explicit casting of string literals to a known type generally > required by PostgreSQL? > > TIA, > > Stephen Jackson > Home: [EMAIL PROTECTED] www.panting-deer.org.uk > Work: [EMAIL PROTECTED]www.looksystems.co.uk > > [begin examples] >version > - > PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 > (1 row) > > DROP TABLE strange > DROP > CREATE TABLE strange ( record_id integer ) > CREATE > INSERT INTO strange ( record_id ) VALUES ( 10 ) > INSERT 5174249 1 > SELECT DISTINCT record_id, 123 > FROM strange WHERE record_id < 100 > record_id | ?column? > ---+-- > 10 | 123 > (1 row) > > SELECT DISTINCT record_id, 'Hello' > FROM strange WHERE record_id < 100 > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > Use an explicit ordering operator or modify the query > SELECT record_id, 'Hello' > FROM strange WHERE record_id < 100 > record_id | ?column? > ---+-- > 10 | Hello > (1 row) > > SELECT COUNT(*) FROM strange > WHERE 'TR' || 'UE' = 'TRUE' > count > --- > 1 > (1 row) > > SELECT COUNT(*) FROM strange > WHERE 'TRUE' = 'TRUE' > ERROR: Unable to identify an operator '=' for types 'unknown' and > 'unknown' > You will have to retype this query using an explicit cast > > [end examples] > ---(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] Implicit/Explicit casting of the unknown type
Steve, Hmmm ... I'm using PostgreSQL for quite a few contortions of data, and I can't say that I've encountered anything quite like the problems you're having. It may be that you actually have a compile problem with Postgres rather than a SQL problem. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
