Re: [GENERAL] Row values
On Fri, Jul 02, 2004 at 09:56:43AM -0300, Juan Jose Costello Levien wrote: > Alvaro, > > Sorry, but, where are the log file and core you mentioned? I tried > /var/log/postgresql but is in 0 file size. Your server may be misconfigured (most linux distros are). Make sure your init script does not redirect postmaster's (or pg_ctl's) stdout/stderr to /dev/null. Also make sure that if you are using syslog, the syslog server is configured to save the messages somewhere useful, by checking the facility Postgres uses (configurable) and where does syslog save messages from this facility. The core file, on the other hand, should be somewhere in /var/lib/pgsql/data/base (if you haven't changed PGDATA), _unless_ the server runs under a core 0-byte limit (under bash, ulimit -c will tell you). Hope this helps, -- Alvaro Herrera () "Java is clearly an example of a money oriented programming" (A. Stepanov) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Interpreting query plan
On Fri, 2 Jul 2004, Chris Smith wrote: > I've just noticed in the regular profiling information from our web > application that a particular query on a fairly small database is taking about > 15 seconds. The query is generated from software on the fly, hence its > quirkiness -- if any of that is the problem, then I'll go ahead and fix it, > but I can't imagine a few repeated WHERE conditions fooling the query > optimizer. > > Anyway, I don't know how to interpret query plans. Can anyone give me a hand? > To get the plan, I just plugged in various values -- The actual query is run > with various different values, and even a few different lengths for the IN > clause. > > miqweb=> explain select distinct t0.* from UserAccount t0, UserMapping t1 > where > (t0.companyid = 123) and ((t0.companyid = 123) and (t0.userid = t1.userid) > and > (t1.groupid in (123, 234, 345, 456))) and (t0.companyid = 123); Plain explain output is useful for finding what the plan is, but not as useful for determining why a query takes a particular amount of time. You might want to use "explain analyze" and send that result (which gives the real time and real number of rows for different steps). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] incremental backups?
On 6/22/2004 11:51 PM, mike g wrote: Slony version 1 is supposed to be live very soon. You can test beta3 if you like. Slony-I version 1.0 is out now. It does not contain incremental backup. This feature is on the TODO list for 1.1. Jan Perhaps pgpool could help you. Version 2 was just released. On Tue, 2004-06-22 at 22:28, Joel Matthew wrote: My boss was asking about incremental backups. I was scratching my head, thinking that the transaction log and a backup policy (script) for each record set (sorry about the archaic terminology) was the usual solution. But there is a some resistance against writing more code, so I'm wondering what the current state of affairs with postgresql in regards to incremental backup would be. A quick search of the lists produced the following: Bruce talks in November 2002 about plans for point-in-time recovery in v. 7.4, but last December says it isn't there yet. Jan mentions Slony-I replication back last January. Somebody threw in some chatter about XLog. Scott metioned briefly last August the possibility of combining a live data server with an archive server, or of using a daily schema. What's the typical user doing for incrementals, besides going to a commercial server? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Interpreting query plan
I've just noticed in the regular profiling information from our web
application that a particular query on a fairly small database is taking about
15 seconds. The query is generated from software on the fly, hence its
quirkiness -- if any of that is the problem, then I'll go ahead and fix it,
but I can't imagine a few repeated WHERE conditions fooling the query
optimizer.
Anyway, I don't know how to interpret query plans. Can anyone give me a hand?
To get the plan, I just plugged in various values -- The actual query is run
with various different values, and even a few different lengths for the IN
clause.
miqweb=> explain select distinct t0.* from UserAccount t0, UserMapping t1
where
(t0.companyid = 123) and ((t0.companyid = 123) and (t0.userid = t1.userid)
and
(t1.groupid in (123, 234, 345, 456))) and (t0.companyid = 123);
QUERY PLAN
--
--
--
Unique (cost=133.78..133.81 rows=1 width=55)
-> Sort (cost=133.78..133.79 rows=1 width=55)
Sort Key: t0.userid, t0.companyid, t0.username, t0."password",
t0.isact
ive, t0.isregistered, t0.lastlogin, t0.firstname, t0.lastname
-> Hash Join (cost=13.44..133.77 rows=1 width=55)
Hash Cond: ("outer".userid = "inner".userid)
-> Seq Scan on usermapping t1 (cost=0.00..120.26 rows=13
width=
4)
Filter: ((groupid = 123) OR (groupid = 234) OR (groupid =
3
45) OR (groupid = 456))
-> Hash (cost=13.43..13.43 rows=4 width=55)
-> Index Scan using useraccount_lookup on useraccount t0
(cost=0.00..13.43 rows=4 width=55)
Index Cond: (companyid = 123)
(10 rows)
And relevant tables (apparently a little messed up by prior database version
upgrades, so that come of the foreign keys show up directly as triggers):
miqweb=> \d useraccount
Table "public.useraccount"
Column| Type | Modifiers
--+-+---
userid | integer | not null
companyid| integer | not null
username | text| not null
password | text| not null
isactive | boolean | not null
isregistered | boolean | not null
lastlogin| date|
firstname| text|
lastname | text|
Indexes:
"useraccount_pkey" primary key, btree (userid)
"useraccount_lookup" unique, btree (companyid, username)
Triggers:
"RI_ConstraintTrigger_255906" AFTER INSERT OR UPDATE ON useraccount FROM
com
pany NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_
check_ins"('useraccount_fk1', 'useraccount', 'company', 'UNSPECIFIED',
'companyi
d', 'companyid')
"RI_ConstraintTrigger_255916" AFTER DELETE ON useraccount FROM
registrationf
ield NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_
noaction_del"('registrationfield_fk2', 'registrationfield', 'useraccount',
'UNSP
ECIFIED', 'userid', 'userid')
"RI_ConstraintTrigger_255917" AFTER UPDATE ON useraccount FROM
registrationf
ield NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_
noaction_upd"('registrationfield_fk2', 'registrationfield', 'useraccount',
'UNSP
ECIFIED', 'userid', 'userid')
"RI_ConstraintTrigger_255919" AFTER DELETE ON useraccount FROM userrole
NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_
del"('userrole_fk1', 'userrole', 'useraccount', 'UNSPECIFIED', 'userid',
'userid
')
"RI_ConstraintTrigger_255920" AFTER UPDATE ON useraccount FROM userrole
NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_
upd"('userrole_fk1', 'userrole', 'useraccount', 'UNSPECIFIED', 'userid',
'userid
')
"RI_ConstraintTrigger_255928" AFTER DELETE ON useraccount FROM visit NOT
DEF
ERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_del
"('visit_fk1', 'visit', 'useraccount', 'UNSPECIFIED', 'userid', 'userid')
"RI_ConstraintTrigger_255929" AFTER UPDATE ON useraccount FROM visit NOT
DEF
ERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_upd
"('visit_fk1', 'visit', 'useraccount', 'UNSPECIFIED', 'userid', 'userid')
"RI_ConstraintTrigger_255940" AFTER DELETE ON useraccount FROM adminvisit
NO
T DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noactio
n_del"('adminvisit_fk1', 'adminvisit', 'useraccount', 'UNSPECIFIED', 'userid',
'
userid')
"RI_ConstraintTrigger_255941" AFTER UPDATE ON useraccount FROM adminvisit
NO
T DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noactio
n_upd"('adminvisit_fk1', 'adminvisit', 'useraccount', 'UNSPECIFIED', 'userid',
'
userid')
miqweb=> \d usermapping
Table "public.usermapping"
Column | Type | Modifiers
-+-+---
userid | integer | not null
groupid | integer
Re: [GENERAL] per-session (or persistent) table (and column) aliases
David Garamond wrote: > CREATE TABLE somereallylongname1 (...); > CREATE TABLEALIAS name1 somereallylongname1; > SELECT * FROM name1 ...; > > Is there such a thing? A view does exactly what you need. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] per-session (or persistent) table (and column) aliases
CREATE TABLE somereallylongname1 (...); CREATE TABLEALIAS name1 somereallylongname1; SELECT * FROM name1 ...; Is there such a thing? I know there's alias in SELECT and completion in psql (or even views). But this is more like a filesystem symlink/hardlink. Would this be cool/useful? -- dave ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] How to add an userA to login postgresadmin
stephen, try adding the user like you normally would. Did you know the system users and database users are not necessarily the same person? With that in mind, after adding the user, create an alias in .bashrc like alias psql='/usr/local/psql -U postgresadmin -d my_db_name' Furthermore, in /home/username (that is, that user's ~ home directory) you can put a .pgpass file which contains the password for postgresadmin HOWEVER, it would have been simpler to simply use the 'postgres' system account as it is the universal default On Fri, Jul 02, 2004 at 07:56:09PM +0800, Stephen Liu wrote: > Hi folks, > > RH8.0 > postgresql-7.4.3.tar.gz > > I have postgresql-7.4.3 installed > > $ whereis pgsql > pgsql: /usr/local/pgsql > > and have created "postgresadmin" for root > # adduser postgresadmin > > root can su postgresadmin without password > > Now I expect to add 'userA' to login as postgresadmin > with a password > > Can I perform following step to reach my goal; > > # adduser userA postgresadmin passwd xxx > > Kindly advise. TIA > > B.R > Stephen > > ___ > Do You Yahoo!? > Get your free @yahoo.com.hk address at http://mail.english.yahoo.com.hk > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Row values
Juan Jose Costello Levien <[EMAIL PROTECTED]> writes: > I don't know if tgargs[] return the row values or the field names. Neither --- it's the (fixed) arguments you used in the CREATE TRIGGER command. You'll need to do something involving extracting field values from the tuple that is passed to the trigger, instead. I'd suggest looking in the contrib/ modules for examples of triggers written in C. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Row-level security--is it possible?
On Fri, Jul 02, 2004 at 17:32:07 +0200, Michal Taborsky <[EMAIL PROTECTED]> wrote: > Doug McNaught wrote: > >But why not create a "products_restricted" view that uses the > >CURRENT_USER function to see who's running it? > > > >CREATE VIEW products_restricted AS > >SELECT * FROM products WHERE Producer_ID = get_producer_id(CURRENT_USER); > > > >[CURRENT_USER returns a string, so you would need to map it to your > >producer_id somehow.] > > This would work only for this case (limiting single producer to one > user). But we want to have a bit more flexible system, so we'd be able > define the restrictions freely (like "only producers 1 and 5 and price > less than 100"). I'm sorry I did not mention this. Then you can create a group table matching up producers and authorized users. The view should join the base table with the group table on producer and limit the results to users matching the "current_user". With appropiate indexes this should be fast. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Row-level security--is it possible?
Michal Taborsky <[EMAIL PROTECTED]> writes: > Doug McNaught wrote: >> But why not create a "products_restricted" view that uses the >> CURRENT_USER function to see who's running it? >> CREATE VIEW products_restricted AS >> SELECT * FROM products WHERE Producer_ID = get_producer_id(CURRENT_USER); >> [CURRENT_USER returns a string, so you would need to map it to your >> producer_id somehow.] > > This would work only for this case (limiting single producer to one > user). But we want to have a bit more flexible system, so we'd be able > define the restrictions freely (like "only producers 1 and 5 and price > less than 100"). I'm sorry I did not mention this. Have you looked into set-returning functions for this? That would let you basically put whever logic you need into the function. -Doug ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Row values
Alvaro, Sorry, but, where are the log file and core you mentioned? I tried /var/log/postgresql but is in 0 file size. TIA On Fri, Jul 02, 2004 at 07:36:50AM -0300, Juan Jose Costello Levien wrote: I am writing my first trigger in C for PostgreSQL. It compiles Ok, and added it to the database using CREATE TRIGGER. But when I try to fire it, psql simply says 'The connection was lost". Most likely the server process crashed. See the server log and the core file for details to debug your function. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
