Re: [GENERAL] 8.3 release notes
Robert Treat wrote: > > However, neither one of them show what redirect_stderr was renamed to. It > > says something like "... was renamed to foo. redirect_stderr was renamed > > to bar was renamed to ..." > > Yeah, that should get cleaned up. Looks OK now: http://www.postgresql.org/docs/8.3/static/release-8-3.html -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Recovering data via raw table and field separators
John Wells wrote: > On 12/4/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > > Ah sorry, I though you meant de table was dropped or the database was > > deleted. If you actually ran a DELETE FROM on the table, then yes > > they'll all be marked deleted. > > > So, given a database table file that still has records in it, and > given the fact that these records could be parsed and displayed if the > proper utilty knew how to read the various data structures used to > denote field and record length, is there no utility to do this? I > seems that it would be fairly straight forward to somehow read the > records, yet to pay no mind to the deleted flag (or whatever mechanism > postgresql uses to mark them as deleted). We used to have the C defined MAKE_EXPIRED_TUPLES_VISIBLE that would make deleted rows visible, but it seems it was removed in this commit as part of a restructuring: http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/time/tqual.c.diff?r1=1.95;r2=1.96;f=h Sun Sep 3 15:59:39 2006 UTC (15 months, 1 week ago) by tgl Branches: MAIN Diff to: previous 1.95: preferred, colored Changes since revision 1.95: +100 -66 lines Arrange for GetSnapshotData to copy live-subtransaction XIDs from the PGPROC array into snapshots, and use this information to avoid visits to pg_subtrans in HeapTupleSatisfiesSnapshot. This appears to solve the pg_subtrans-related context swap storm problem that's been reported by several people for 8.1. While at it, modify GetSnapshotData to not take an exclusive lock on ProcArrayLock, as closer analysis shows that shared lock is always sufficient. Itagaki Takahiro and Tom Lane Not sure if we should re-add it for later use. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Transaction isolation and constraints
[EMAIL PROTECTED] wrote: > Hi, Tom: > > >Whichever one manages to get to the index page first will go through. > >The second one will block waiting to see if the first one commits, > >and will error out if so --- or proceed, if it aborts. > > I see, this makes sense. What if the two transactions insert rows > that don't violate the constraint: will they be able to proceed in > parallel? Or will one wait for the other, because they both need to > update the shared index? I.e., does the mechanism work by waiting for > one index update to commit before permitting the next? They proceed in parallel. Backends wait only on specific rows that conflict, not the index page. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] limits
On Dec 15, 2007, at 8:29 PM, Bruce Momjian wrote: [EMAIL PROTECTED] wrote: Hi All. My question is simple and plain: Are there some limit in the number of database operations between a BEGIN statement and a COMMIT statement? Yes, there is a command counter that is incremented for every command between BEGIN and COMMIT. It can't exceed 4 billion commands. I think the error message upon hitting that deserves some kind of easter egg status. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(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
[GENERAL] about new feature
Hi everybody, I' m work on a software to create automatic webservices for stored procedure in any language. It's almost like the explain above: have one table pg_plwebservice Have one sp hello, develope in any languages like sql, plpgsql(trusted or untrusted)like for example. The DBA check this sp to be a webservice. the client request like for example : http:localhost/ the inetd know this is a resquest for plwebservice software and redirect te request. plwebservice is a C++ software and you mission is: understand http request and Webservice default descriptions. connect to database. verify what sp was requested and. Verify the data sended for client and proceed with the sql statment to request postgresql sp. My question is, for all. That is a good idea? Any question and/or advice are welcome. thans for advanced. Ivo Nascimento. -- Iann Desenvolvendo soluções com performance e segurança. --
Re: [GENERAL] limits
[EMAIL PROTECTED] wrote: > Hi All. > My question is simple and plain: Are there some limit in the number of > database operations between a BEGIN statement and a COMMIT statement? Yes, there is a command counter that is incremented for every command between BEGIN and COMMIT. It can't exceed 4 billion commands. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Conditional updateable view
I can't seem to find an example of how to add restrictions to the where clause of an updateable view created via the rule system. For example I don't want the update to complete if a where clause is missing entirely, and in some cases I want to only allow the update if the where clause specifies a particular column. Is there a way to do this? Chris ---(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: [GENERAL] Better alternative for Primary Key then serial??
On Dec 13, 2007, at 14:12, John D. Burger wrote: Alban Hertroys wrote: The problem the OP is pointing out seems difficult to solve. A sequence doesn't know about existing records with a possibly higher number than the sequence is at. This may be worked around by keeping a list of numbers used up beyond the current sequence value so the sequence knows what numbers to skip, but that has problems of its own (if there are many such numbers, or if the sequence gets created after data has been added to the list). It gets ugly. I would just have a trigger that sets the serial to NEW.id + 1. Dunno if this has concurrency issues, though, and it may leave huge gaps in the key space, and (more importantly) use up your sequence too quickly. That thought had crossed my mind, but I rejected it. Besides those issues, you'd need to add that trigger to every table that potentially has this "issue", it does indeed have concurrency issues, and you're in for some fun if someone decides to use the last possible value of a serial field to define a special case (although the sequence could be defined to end before that value of course) and causes immediate wraparound of the sequence (provided it's allowed to rotate) and thus causing duplicate key violations as soon as the sequence matches the first record in the table. I was looking at a general solution that would work as automatic as sequences already do. Not that it's a big issue anyway, you just have to keep in mind that it works like it does. Just to prevent any mistakes, I am not requesting a change in behaviour, I'm fine with how it works (and has worked for ages in many database servers). It is an interesting puzzle though ;) I have, in fact, had situations where I wanted a serial PK, =and= I needed to insert with external IDs sometimes - essentially a mix of natural and surrogate keys (shudder). It turned out that the natural keys were always positive, so I set up the sequence to range =downward= from 0. That's a common solution to the problem, although it leaves the possibility that people are being smart and enter negative integers exactly to prevent this problem. And of course you cannot rely on sorting it by index to get your data more or less in the order inserted. -- Alban Hertroys "If you lose your memory, you can't remember where you left it." !DSPAM:737,476419a19654199211162! ---(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: [GENERAL] postgres cannot access the server configuration file
Thanks a lot for your help. I managed to figure it out. 2007/12/15, Bill Moran <[EMAIL PROTECTED]>: > "Sebastien ARBOGAST" <[EMAIL PROTECTED]> wrote: > > > > 2007/12/15, Bill Moran <[EMAIL PROTECTED]>: > > > "Sebastien ARBOGAST" <[EMAIL PROTECTED]> wrote: > > > > > > > > I'm trying to start postgreSQL server on my Macbook Pro. I've > > > > installed it using packages available here: > > > > http://www.kyngchaos.com/wiki/software:postgres > > > > But when I try to start up the server running "sudo SystemStarter > > > > start PostgreSQL", I get the following message: > > > > > > > > postgres cannot access the server configuration file > > > > "/usr/local/pgsql/data/postgresql.conf": No such file or directory > > > > > > > > And as a matter of fact, there is no such file in this directory. Do I > > > > have to create it manually? > > > > > > You need to run initdb to create the directory: > > > http://www.postgresql.org/docs/8.3/static/app-initdb.html > > > > The problem is that I need the password of the postgres user that has > > been created automatically for me. I've tried "postgres" but it > > doesn't seem to work. And since I can't see the user in my Preference > > Pane, I can't change his password. > > Please don't top-post. And please don't respond personally to email that > was originated on the mailing list. I've returned > pgsql-general@postgresql.org to the list of recipients. > > If you installed the software, you obviously have root access, so just change > the postgres password to something you know. > > While I'm not familiar with the Mac OS installation procedure, I'd assume > that account was created without login capability (probably without a > password at all) which is good, sound security practice. Software shouldn't > create users with known passwords. > > You can also use sudo to switch to the postgres user without needing the > password for the postgres user (although it will probably ask you for the > root password again -- not entirely sure how Mac OS is set up by default) > This approach has become pretty much par for the course on modern POSIX > systems. > > Try: > > sudo -u postgres initdb > > -- > Bill Moran > http://www.potentialtech.com > -- Sébastien Arbogast http://www.sebastien-arbogast.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] query pegs beta4
On Sat, Dec 15, 2007 at 12:39:30PM -0500, Tom Lane wrote: > Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > > This new query of mine pegs beta4, it doesn't return and CPU is at 100%: > > select l.id_location,l.name, > > a.city > > from location l, address a, show_date x, show s, show s2 > > where (l.id_address = a.id_address > > and x.id_location = l.id_location > > and s.id_show = x.id_show > > and s2.show_type = s.show_type and s2.id_show = 305) > > or l.id_location = 172; > > > The tables are not big, at most a few hundred elements each, if that. > > > Maybe the query itself is flawed, > > I'd say so. Any l row with id_location = 172 joins to the cartesian > product of all the other tables. I doubt that's what you meant. Hi Tom, No, what I really meant (and clumsily attempted here) is: either return the list of locations that have been already used for the same 'show_type' as the current show) OR just return the newly created location 172. I just backtracked and expressed the equivalent in perl, so no problem here. Thanks, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] query pegs beta4
Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > This new query of mine pegs beta4, it doesn't return and CPU is at 100%: > select l.id_location,l.name, > a.city > from location l, address a, show_date x, show s, show s2 > where (l.id_address = a.id_address > and x.id_location = l.id_location > and s.id_show = x.id_show > and s2.show_type = s.show_type and s2.id_show = 305) > or l.id_location = 172; > The tables are not big, at most a few hundred elements each, if that. > Maybe the query itself is flawed, I'd say so. Any l row with id_location = 172 joins to the cartesian product of all the other tables. I doubt that's what you meant. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] query pegs beta4
This new query of mine pegs beta4, it doesn't return and CPU is at 100%: select l.id_location,l.name, a.city from location l, address a, show_date x, show s, show s2 where (l.id_address = a.id_address and x.id_location = l.id_location and s.id_show = x.id_show and s2.show_type = s.show_type and s2.id_show = 305) or l.id_location = 172; The tables are not big, at most a few hundred elements each, if that. Maybe the query itself is flawed, I haven't tried it on other versions of postgres. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgres cannot access the server configuration file
"Sebastien ARBOGAST" <[EMAIL PROTECTED]> wrote: > > 2007/12/15, Bill Moran <[EMAIL PROTECTED]>: > > "Sebastien ARBOGAST" <[EMAIL PROTECTED]> wrote: > > > > > > I'm trying to start postgreSQL server on my Macbook Pro. I've > > > installed it using packages available here: > > > http://www.kyngchaos.com/wiki/software:postgres > > > But when I try to start up the server running "sudo SystemStarter > > > start PostgreSQL", I get the following message: > > > > > > postgres cannot access the server configuration file > > > "/usr/local/pgsql/data/postgresql.conf": No such file or directory > > > > > > And as a matter of fact, there is no such file in this directory. Do I > > > have to create it manually? > > > > You need to run initdb to create the directory: > > http://www.postgresql.org/docs/8.3/static/app-initdb.html > > The problem is that I need the password of the postgres user that has > been created automatically for me. I've tried "postgres" but it > doesn't seem to work. And since I can't see the user in my Preference > Pane, I can't change his password. Please don't top-post. And please don't respond personally to email that was originated on the mailing list. I've returned pgsql-general@postgresql.org to the list of recipients. If you installed the software, you obviously have root access, so just change the postgres password to something you know. While I'm not familiar with the Mac OS installation procedure, I'd assume that account was created without login capability (probably without a password at all) which is good, sound security practice. Software shouldn't create users with known passwords. You can also use sudo to switch to the postgres user without needing the password for the postgres user (although it will probably ask you for the root password again -- not entirely sure how Mac OS is set up by default) This approach has become pretty much par for the course on modern POSIX systems. Try: sudo -u postgres initdb -- Bill Moran http://www.potentialtech.com ---(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: [GENERAL] increasing checkpoint_timeout?
Oh, and another point I forgot to mention, is that you'd need to increase checkpoint segments to get a longer time between checkpoints as well. But honestly, do a search on the archives for tuning the background writer, it's a much better option for most workloads. ---(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: [GENERAL] increasing checkpoint_timeout?
On Dec 15, 2007 6:51 AM, rihad <[EMAIL PROTECTED]> wrote: > http://www.postgresql.org/docs/8.3/static/wal-configuration.html > > Is it right that checkpoint_timeout means the amount of time up to which > you agree to lose data in the event of a power crash? No, dear god, no. :) Once something is committed, it won't be lost due to a server crash unless your hardware is lying about fsync. The data that hasn't been checkpointed has to be replayed upon a crash / restart, that's all. > What if I set it > to 1 hour (and bump checkpoint_segments accordingly), does it mean that > I'm willing to lose up to 1 hour of data? Nope, but it could take a very long time to recover from a crash / kill -9 situation as all that data gets replayed. > I'm thinking about increasing > checkpoint_timeout to mitigate the full_page_writes bloat. You'd be better off working on tuning the background writer so that you lose some small% of performance all the time but never have huge spike in checkpointing. > BTW how are transactions WAL logged? Do the logs include data too? In > this case, am I right that the effects of full_page_writes=on serve as a > starting data page on top of which to replay transactions when doing > crash recovery? full_page_writes = on = you CAN recover from a server crash / kill -9. Off not so much guaranteed. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgres cannot access the server configuration file
"Sebastien ARBOGAST" <[EMAIL PROTECTED]> wrote: > > I'm trying to start postgreSQL server on my Macbook Pro. I've > installed it using packages available here: > http://www.kyngchaos.com/wiki/software:postgres > But when I try to start up the server running "sudo SystemStarter > start PostgreSQL", I get the following message: > > postgres cannot access the server configuration file > "/usr/local/pgsql/data/postgresql.conf": No such file or directory > > And as a matter of fact, there is no such file in this directory. Do I > have to create it manually? You need to run initdb to create the directory: http://www.postgresql.org/docs/8.3/static/app-initdb.html -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] postgres cannot access the server configuration file
I'm trying to start postgreSQL server on my Macbook Pro. I've installed it using packages available here: http://www.kyngchaos.com/wiki/software:postgres But when I try to start up the server running "sudo SystemStarter start PostgreSQL", I get the following message: postgres cannot access the server configuration file "/usr/local/pgsql/data/postgresql.conf": No such file or directory And as a matter of fact, there is no such file in this directory. Do I have to create it manually? -- Sébastien Arbogast http://www.sebastien-arbogast.com ---(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
[GENERAL] increasing checkpoint_timeout?
http://www.postgresql.org/docs/8.3/static/wal-configuration.html Is it right that checkpoint_timeout means the amount of time up to which you agree to lose data in the event of a power crash? What if I set it to 1 hour (and bump checkpoint_segments accordingly), does it mean that I'm willing to lose up to 1 hour of data? I'm thinking about increasing checkpoint_timeout to mitigate the full_page_writes bloat. BTW how are transactions WAL logged? Do the logs include data too? In this case, am I right that the effects of full_page_writes=on serve as a starting data page on top of which to replay transactions when doing crash recovery? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] setting and using variables in PSQL ????
Gauthier, Dave <[EMAIL PROTECTED]> schrieb:
>
>
> Hi:
>
> At the PSQL prompt, I want to set some variables based upon query results, or
> via static assignment, then insert a record with those values. Sort of
> like...
You can use this:
- define in your postgresql.conf:
custom_variable_classes = 'myvar'
- use within psql:
test=# set myvar.benutzer = 'foo';
SET
test=*# select * from foo;
id | name
+--
1 | foo
2 | bar
(2 rows)
test=*# select * from foo where name=current_setting('myvar.benutzer');
id | name
+--
1 | foo
(1 row)
test=*# set myvar.benutzer = 'none';
SET
test=*# select * from foo where name=current_setting('myvar.benutzer');
id | name
+--
(0 rows)
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
