[SQL] Index wonder
I just wanted to share my revelation on how an index can do wonders for a query: pgslekt=> explain select child_fk, get_coparent(570,child_fk), get_pbdate(child_fk) as pbd from relations where parent_fk = 570 order by pbd; QUERY PLAN - Sort (cost=378.26..378.27 rows=5 width=4) Sort Key: get_pbdate(child_fk) -> Seq Scan on relations (cost=0.00..378.20 rows=5 width=4) Filter: (parent_fk = 570) (4 rows) pgslekt=> create index parent_key on relations(parent_fk); CREATE INDEX pgslekt=> create index child_key on relations(child_fk); CREATE INDEX pgslekt=> explain select child_fk, get_coparent(570,child_fk), get_pbdate(child_fk) as pbd from relations where parent_fk = 570 order by pbd; QUERY PLAN - Sort (cost=13.81..13.83 rows=5 width=4) Sort Key: get_pbdate(child_fk) -> Index Scan using parent_key on relations (cost=0.00..13.76 rows=5 width=4) Index Cond: (parent_fk = 570) (4 rows) As a consequence, the time for generating a page listing the descendants and their spouses for a singularly prodigius and well-researched family - in total about 1100 persons - went down from 30 seconds to 3. I am thoroughly amazed. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL]
On Wed, 2005-11-23 at 23:23 -0500, Ken Winter wrote: > In PL/pgSQL, is there a way to put a *variable* column-name in a dot > notation reference to a RECORD column? > > For example, suppose I want to write a function like the following, which is > to be called by a "BEFORE INSERT" trigger: > > CREATE OR REPLACE FUNCTION foo ( ) RETURNS TRIGGER AS > ' > DECLARE > var VARCHAR; > BEGIN > var := TG_ARGV[0] > NEW. := ''whatever''; > RETURN NEW; > END; > ' > LANGUAGE 'plpgsql' > ; > > The aim of this uninteresting function is to assign the value 'whatever' to > the table column that is passed in by the calling trigger as TG_ARGV[0], > i.e. the first calling argument. > > What I don't know is what to put into the dot notation in place of ". column whose name is the value of var>" so that the column of NEW that is > addressed by the assignment statement is the one passed in as the first > argument. Is there any PL/pgSQL construct that could be substituted in here > to achieve this result? Unfortunately not. > If not, can anybody suggest a way to write a trigger-called function that > would accomplish the same result? You would have to do something like: CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$ DECLARE var VARCHAR; BEGIN var := TG_ARGV[0]; IF var = 'column_1' THEN NEW.column_1 = 'whatever'; ELSIF var = 'column_2' THEN NEW.column_2 = 'whatever'; ... END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; Oliver Elphick ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL]
O Oliver Elphick έγραψε στις Nov 24, 2005 : > On Wed, 2005-11-23 at 23:23 -0500, Ken Winter wrote: > > In PL/pgSQL, is there a way to put a *variable* column-name in a dot > > notation reference to a RECORD column? You can do it with a C function. (See dbmirror's pending.c for a reference) > -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] does indexes need statistics?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello everybody, Is it necesary the statistics be enabled when I create indexes or to use them? thanks Mauricio Fernández A. Ingeniero de Sistemas U. Autónoma de Manizales -BEGIN PGP SIGNATURE- Version: PGPfreeware 6.0.2i iQA/AwUBQ4X7u84irmZP1c6WEQLdzgCfS0S7kfz6DOdv1pPd9w9iBvKpYaYAn3bL efk9bmafKvgTFjUxWZV1jGR/ =I7D6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Updatable views: any decent front-ends?
(As this seems to be the general pg list, I'm posting here even though it is a front-end issue. Apologies.) While I can happily create rules on views to allow inserts, updates and deletes, I can't find a GUI front-end that understands that the view allows record edits that I can run on linux (whether through X or web-based doesn't matter) and simply open the relation and edit data without designing horrible forms with lots of code. I note that MS-Access allows this, as it asks for the field(s) that are unique and can be used for updating when you "attach" a table, but I don't have (or want) a Windows box or MS-Office. So 1) Does anybody know of a tool that allows easy editing of data in views? 2) Might it be useful to have a "contrib" module that has additional table that stores such information in a way the tools such as pgadmin, phpPgAdmin, OpenOffice etc could leverage? This would possibly be in a new well-known schema, maybe "accessory_catalog" and be a bit like the optional schema used by pgadmin. 3) Has anybody hacked common front-ends (such as phpPgAdmin) to do something like this. If I have to develop a hack (probably to phpPgAdmin), I will, but I'd be taking approach (2) as part of the solution, and would offer it to "contrib" so other pg-related tools could use it. The other thing I'd put into it would be an association between relation, attributes and short strings suitable for default screen prompts and report headings. I am being wrong-headed here? -- David T. Bath [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Changing auth type in pg_hba.conf ineffective. Why?
I'm struggling to resolve a login error. It seems that I'm getting "ident" authentication, no mater what I set pg_hba.conf to. Here's a log file: LOG: next transaction ID: 602; next OID: 17232 LOG: database system is ready ... FATAL: Ident authentication failed for user "bryce" LOG: could not connect to Ident server at address "::1", port 113: Connection refused This makes sense, because no identd is running, and port 113 is blocked anyway. So I need another auth method. My relevant pg_hba.conf lines are: #local all all ident sameuser #hostall all 127.0.0.1/32 ident sameuser #hostall all ::1/128 ident sameuser local all all md5 hostall all 127.0.0.1/32trust hostall all ::1/128 md5 I've renamed pg_hba.conf temporarily, just to verify that postmaster won't start without it. So I'm editing the right file, and restarting postmaster correctly. Any clues why I'm still getting 'auth' method authentication? Environment: I'm using JDBC (Java Database Connection) PostgreSQL version 8.0.3 SUSE Linux 10.0 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Changing auth type in pg_hba.conf ineffective. Why?
Bryce Nesbitt wrote: > I've renamed pg_hba.conf temporarily, just to verify that postmaster > won't start without it. So I'm editing the right file, and restarting > postmaster correctly. Any clues why I'm still getting 'auth' method > authentication? What message do you get if you try to connect while postmaster is down? I'm just trying to make sure you are connecting to the postmaster you think you are ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: 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] Changing auth type in pg_hba.conf ineffective. Why?
Bryce Nesbitt <[EMAIL PROTECTED]> writes: > I've renamed pg_hba.conf temporarily, just to verify that postmaster > won't start without it. So I'm editing the right file, and restarting > postmaster correctly. Any clues why I'm still getting 'auth' method > authentication? Well, have you checked that you're connecting to the same postmaster you're restarting? After that, I'd ask to see the whole pg_hba.conf and not just the part you think is relevant ;-) FWIW, the log message suggests that the connection is coming across IPv6, so you need to look at the IPv6 entries in pg_hba.conf. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Changing auth type in pg_hba.conf ineffective. Why?
In that case I get: ***Exception: org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections Alvaro Herrera wrote: > What message do you get if you try to connect while postmaster is down? > I'm just trying to make sure you are connecting to the postmaster you > think you are ... > >> Bryce Nesbitt: >> I've renamed pg_hba.conf temporarily, just to verify that postmaster >> won't start without it. So I'm editing the right file, and restarting >> postmaster correctly. Any clues why I'm still getting 'auth' method >> authentication? >> ---(end of broadcast)--- TIP 1: 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] Changing auth type in pg_hba.conf ineffective. Why?
IPv6? Really? That new fangled thing? That's enabled on my machine by default? Whadda know. That's it. Thanks. Tom Lane wrote: > Bryce Nesbitt <[EMAIL PROTECTED]> writes: > >> I've renamed pg_hba.conf temporarily, just to verify that postmaster >> won't start without it. So I'm editing the right file, and restarting >> postmaster correctly. Any clues why I'm still getting 'auth' method >> authentication? >> > > Well, have you checked that you're connecting to the same postmaster > you're restarting? > > After that, I'd ask to see the whole pg_hba.conf and not just the part > you think is relevant ;-) > > FWIW, the log message suggests that the connection is coming across > IPv6, so you need to look at the IPv6 entries in pg_hba.conf. > > regards, tom lane > ---(end of broadcast)--- TIP 1: 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
