[ADMIN] Problems with PGOPTIONS
Hi all, I have a devel server where i want to turn on collector statistics. I want to enable the collection of STATS_ROW_LEVEL for special backends by setting the environment-variable export PGOPTIONS='-c STATS_ROW_LEVEL=ON'. After setting this variable i cant connect to my database. Everytime the following error occurs: Connection to database 'mydb' failed. FATAL 1: permission denied After unset PGOPTIONS everything works without any problems. I have tested the same with an example C-program found under http://www.postgresql.org/idocs/ but always got the same error if i want to send additional options. Did i make some mistakes ? Thanks in advance for help as ---(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
[ADMIN] How Copy 7.3 Schemas?
I am playing with the new Schema feature in 7.3. I would like to copy the public schema into a new schema. Is there an easy way to do this? Something I can do with pg_dump (I don’t see a switch to specify which schema) Right now I an only interested in copying the structures – later on it would be nice if I could copy the data as well. Regards
[ADMIN] [OT?] Derived attributes
Hi folks, sorry if this might seem a bit too specific to WebObjects: I've used a couple of derived attributes in my WebObjects app's EOModel. Simple stuff along the lines of: (SELECT COUNT(*) FROM address WHERE address.eoid_customer = customer.eoid) in the Customer entity. (eoid being pk and eoid_customer fk) This worked nicely with OpenBase, i.e. it simply returned the number of addresses a particular customer has registered. Using the same expression with Postgres returns in fact the row count of the address table. Is there a way to make Postgres use the eoid of the current row instead of checking for matches between eoid_customer and _any_ eoid in the customer table? Any hints greatly appreciated! Fabian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Query failing with strange error.
In the middle of a script, inside a transaction, I run the following query: update customer_contact set next_billed=min(customer_services.eff_date) where customer_contact.conn_num=7698 and customer_services.conn_num=7698 and customer_services.inv_num=0 I have also tried changing this to update customer_contact set next_billed=min(customer_services.eff_date) where customer_contact.conn_num=7698 and customer_services.conn_num=customer_contact.conn_num and customer_services.inv_num=0 ... which changes the query plan, but both give the following error: ERROR: ExecutePlan: (junk) `ctid' is NULL! Now... I tried dumping and reloading the database. Then I upgraded from 7.2.1 to 7.2.3 (and reloaded the database from scratch). Neither worked. The odd part about the error is that this query gets executed 100's of times a day with different conn_num values. Only this conn_num gives the error. This conn_num exists in both tables. eff_date has a senable value (it's a 'timestamp with timezone'). In fact: sales=# select min(next_billed),min(customer_services.eff_date) from customer_contact, customer_services where customer_contact.conn_num=7698 and customer_services.conn_num=7698 and customer_services.inv_num=0; min | min + 2003-01-23 | 2003-01-23 00:00:00-05 (1 row) Anyone got any ideas? Dave. -- |David Gilbert, Velocet Communications. | Two things can only be | |Mail: [EMAIL PROTECTED] | equal if and only if they | |http://daveg.ca | are precisely opposite. | =GLO ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Problems with PGOPTIONS
Andre Schubert <[EMAIL PROTECTED]> writes: > I want to enable the collection of STATS_ROW_LEVEL for special backends > by setting the environment-variable export PGOPTIONS='-c STATS_ROW_LEVEL=ON'. > After setting this variable i cant connect to my database. > Everytime the following error occurs: > Connection to database 'mydb' failed. > FATAL 1: permission denied You cannot alter stats_row_level unless you're a superuser (and I think even that exception doesn't work for PGOPTIONS, because it's processed before the backend has determined whether you are a superuser). You'd get the same error if you tried regression=> set STATS_ROW_LEVEL = on; ERROR: 'stats_row_level': permission denied 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: [ADMIN] Problems with PGOPTIONS
On Wed, 15 Jan 2003 10:01:27 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Andre Schubert <[EMAIL PROTECTED]> writes: > > I want to enable the collection of STATS_ROW_LEVEL for special backends > > by setting the environment-variable export PGOPTIONS='-c STATS_ROW_LEVEL=ON'. > > After setting this variable i cant connect to my database. > > Everytime the following error occurs: > > > Connection to database 'mydb' failed. > > FATAL 1: permission denied > > You cannot alter stats_row_level unless you're a superuser (and I think > even that exception doesn't work for PGOPTIONS, because it's processed > before the backend has determined whether you are a superuser). You'd > get the same error if you tried > > regression=> set STATS_ROW_LEVEL = on; > ERROR: 'stats_row_level': permission denied > Ok, but is there another way to enable STATS_ROW_LEVEL for a special backend not connecting as superuser ? Regards, as ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Query failing with strange error.
David Gilbert <[EMAIL PROTECTED]> writes: > update customer_contact > set next_billed=min(customer_services.eff_date) > where customer_contact.conn_num=7698 > and customer_services.conn_num=7698 > and customer_services.inv_num=0 This is not a well-defined query --- exactly what do you think the semantics should be? Over what set of rows is the MIN() taken, for any particular target row to be updated? With only one WHERE clause, you've got no way to control the set of rows the MIN() scans separately from the set of rows the UPDATE targets. SQL92 forbids such things outright: ::= UPDATE SET [ WHERE ] Syntax Rules 2) A in a shall not directly con- tain a . Postgres doesn't presently forbid it, but we probably should, because the executor tends to get confused --- unsurprisingly considering that there's no well-defined behavior for this case. What I think you mean is update customer_contact set next_billed = (SELECT min(customer_services.eff_date) FROM customer_services where customer_services.conn_num=7698 and customer_services.inv_num=0) where conn_num=7698 but that's just a guess about the intended behavior. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] pg_dumpall and large flat file
Title: Gareth Stationery Hey everyone. We've got to re-install a server shortly, and I'm trying to do a dry run of reloading their data onto a similar server. It's Postgres 7.1.3 on Redhat Linux ( 6.2 or 7.2 - not too important ) We used pg_dumpall > large to create a file that's just under 1Gb in size. Obviously there's no vi'ing this and little hope of cat | less. We can split the file with split -l 10 if we need to. We want to now reload this database cluster. The questions: 1) Is pg_dumpall the best way of doing this ( it's the only way I can find ) 2) Is pgsql -f large template1 the best way of reloading it 3) How long should it take ( 128mb of ram, 1ghz processor ) - roughly ( hours - days!? :-D ) Thanks everyone Gareth KirwanProgramming & Development,Thermeon Europe Ltd,[EMAIL PROTECTED] Tel: +44 (0) 1293 864 303Thermeon Europe e-Card: gbjk
Re: [ADMIN] Problems with PGOPTIONS
Andre Schubert <[EMAIL PROTECTED]> writes: > Ok, but is there another way to enable STATS_ROW_LEVEL for a special > backend not connecting as superuser ? No: the code is deliberately designed to prevent that, on the grounds that it'd be a security flaw (non-superusers should not be allowed to hide their activities from the DBA). There's nothing to stop you from making a private version with stats_row_level downgraded to an ordinary unprotected SET variable, though, if that seems more appropriate to your needs. See its entry in src/backend/utils/misc/guc.c and change it from SUSET to USERSET category (from memory; check spelling in src/include/utils/guc.h). regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] How Copy 7.3 Schemas?
A drity method could be. create table schema2.table2 as select * from schema1.table1 ; in a loop that uses system catalogs. regds mallah. On Wednesday 15 January 2003 06:08 pm, Lex wrote: > I am playing with the new Schema feature in 7.3. I would like to copy > the public schema into a new schema. Is there an easy way to do this? > Something I can do with pg_dump (I dont see a switch to specify which > schema) Right now I an only interested in copying the structures > later on it would be nice if I could copy the data as well. > > > > Regards -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
