Re: [HACKERS] Pregunta sobre limitar el
On Tue, March 14, 2006 22:14, Paulina Quezada wrote: > Hola, necesito saber con que sentencias sql puedo controlar desde un > trigger > el número de registros a actualizar o a borrar, esto para que desde la > consola no se hagan deletes o updates masivos. Aqui se habla ingles. Jeroen ---(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: [HACKERS] Static build of psql with readline support
Christopher Kings-Lynne wrote:
Hi guys,
I've been trying to build the cvs checkout of 8.1.3 on my freebsd 4.9
box with a STATIC psql utility. I keep getting failures trying to hook
in libreadline I think:
lreadline -lcrypt -lcompat -lm -lutil -o psql
/usr/lib/libreadline.a(terminal.o): In function `_rl_get_screen_size':
terminal.o(.text+0x84): undefined reference to `tgetnum'
terminal.o(.text+0xdd): undefined reference to `tgetnum'
/usr/lib/libreadline.a(terminal.o): In function `rl_resize_terminal':
terminal.o(.text+0x1ce): undefined reference to `tgetstr'
/usr/lib/libreadline.a(terminal.o): In function `_rl_init_terminal_io':
terminal.o(.text+0x2c6): undefined reference to `tgetent'
terminal.o(.text+0x4a9): undefined reference to `tgetflag'
...more...
It builds fine if I use --disable-readline, but other than that I simply
can't get it to build. I've done gmake distclean, reconfigured, etc.
How do I get this to work? I've basically just added '-static' to the
psql Makefile, eg: 'psql: ${CC} -static ...'
I can build static pg_dump and pg_dumpall just fine (they don't use
readline though of course.)
Add a -lcurses as well, as it seems that as soon as you force static,
libreadline needs to be told explicitly about libcurses. I'm on FreeBSD
6.0, but hopefully this is what is going on on 4.9 too.
Cheers
Mark
---(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: [HACKERS] Static build of psql with readline support
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > I've been trying to build the cvs checkout of 8.1.3 on my freebsd 4.9 > box with a STATIC psql utility. I keep getting failures trying to hook > in libreadline I think: > lreadline -lcrypt -lcompat -lm -lutil -o psql > /usr/lib/libreadline.a(terminal.o): In function `_rl_get_screen_size': > terminal.o(.text+0x84): undefined reference to `tgetnum' > terminal.o(.text+0xdd): undefined reference to `tgetnum' You seem to be missing the termcap or curses library in your link. readline requires whichever of those your platform has. 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
[HACKERS] Static build of psql with readline support
Hi guys,
I've been trying to build the cvs checkout of 8.1.3 on my freebsd 4.9
box with a STATIC psql utility. I keep getting failures trying to hook
in libreadline I think:
lreadline -lcrypt -lcompat -lm -lutil -o psql
/usr/lib/libreadline.a(terminal.o): In function `_rl_get_screen_size':
terminal.o(.text+0x84): undefined reference to `tgetnum'
terminal.o(.text+0xdd): undefined reference to `tgetnum'
/usr/lib/libreadline.a(terminal.o): In function `rl_resize_terminal':
terminal.o(.text+0x1ce): undefined reference to `tgetstr'
/usr/lib/libreadline.a(terminal.o): In function `_rl_init_terminal_io':
terminal.o(.text+0x2c6): undefined reference to `tgetent'
terminal.o(.text+0x4a9): undefined reference to `tgetflag'
...more...
It builds fine if I use --disable-readline, but other than that I simply
can't get it to build. I've done gmake distclean, reconfigured, etc.
How do I get this to work? I've basically just added '-static' to the
psql Makefile, eg: 'psql: ${CC} -static ...'
I can build static pg_dump and pg_dumpall just fine (they don't use
readline though of course.)
Chris
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] Pregunta sobre limitar el número de registros a borrar...
Hola, necesito saber con que sentencias sql puedo controlar desde un trigger el número de registros a actualizar o a borrar, esto para que desde la consola no se hagan deletes o updates masivos. Gracias!
Re: [HACKERS] [PERFORM] BETWEEN optimizer problems with single-value range
On 3/15/06, Andreas Kretschmer <[EMAIL PROTECTED]> wrote: > Merlin Moncure <[EMAIL PROTECTED]> schrieb: > > > On 3/15/06, Kevin Grittner <[EMAIL PROTECTED]> wrote: > > > Attached is a simplified example of a performance problem we have seen, > > > with a workaround and a suggestion for enhancement (hence both the > > > performance and hackers lists). > > > > > > Hi Kevin. In postgres 8.2 you will be able to use the row-wise > > 8.2? AFAIK, Feature freeze in juni/juli this year... > Release august/september. yes, but I was addressing kevin's point about enhancing the server... > > comparison for your query which should guarantee good worst case > > performance without having to maintain two separate query forms. it > > Perhaps, a bitmap index scan (since 8.1) are useful for such querys. > Thats why i asked which version. I think you will find that reading a range of records from a table ordered by an index utilizing the 8.2 comparison feature is much faster than a bitmap index scan. Merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PERFORM] BETWEEN optimizer problems with single-value range
On 3/15/06, Kevin Grittner <[EMAIL PROTECTED]> wrote:
> Attached is a simplified example of a performance problem we have seen,
> with a workaround and a suggestion for enhancement (hence both the
> performance and hackers lists).
Hi Kevin. In postgres 8.2 you will be able to use the row-wise
comparison for your query which should guarantee good worst case
performance without having to maintain two separate query forms. it
is also a more elegant syntax as you will see.
SELECT "CA"."calDate", "CA"."startTime"
FROM "Cal" "CA"
WHERE ("CA"."ctofcNo", "CA"."calDate") BETWEEN
(2192, '2006-03-15') and (2192, '2006-03-15')
ORDER BY "ctofcNo", "calDate", "startTime";
Be warned this will not work properly in pg < 8.2. IMO, row-wise is
the best way to write this type of a query. Please note the row
constructor and the addition of ctofcNo into the order by clause to
force use of the index.
Merlin
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] BETWEEN optimizer problems with single-value range
Attached is a simplified example of a performance problem we have seen, with a workaround and a suggestion for enhancement (hence both the performance and hackers lists). Our software is allowing users to specify the start and end dates for a query. When they enter the same date for both, the optimizer makes a very bad choice. We can work around it in application code by using an equality test if both dates match. I think the planner should be able to make a better choice here. (One obvious way to fix it would be to rewrite "BETWEEN a AND b" as "= a" when a is equal to b, but it seems like there is some underlying problem which should be fixed instead (or in addition to) this. The first query uses BETWEEN with the same date for both min and max values. The second query uses an equality test for the same date. The third query uses BETWEEN with a two-day range. In all queries, there are less than 4,600 rows for the specified cotfcNo value out of over 18 million rows in the table. We tried boosting the statistics samples for the columns in the selection, which made the estimates of rows more accurate, but didn't change the choice of plans. -Kevin between-optimization-problem.txt Description: Binary data ---(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: [HACKERS] constraints and sql92 information_schema compliance
On Wed, 15 Mar 2006, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > The main options seem to be: > > When we're allowing other order access, immediately reorder the > > constraint information to match the primary key order. This helps out > > with IS since the loaded constraint should display properly, but > > theoretically could change the visual representation after load for people > > who don't care about this option. > > Change the representation unconditionally on dump. Basically reorder the > > constraint at dump time to always generate a dump in SQL03 order. This has > > the same downside as the above except only after another dump/restore. > > Change the representation on dump only if the flag is set (probably > > exporting this as an option to pg_dump as well). This could be a little > > more difficult to use, but pretty much causes the user to drive the > > choice. > > I'm missing something. On what basis do you claim that there's a > "SQL03 order", ie some ordering mandated by the spec? What problem is > this really solving? SQL2003 seems to change the relevant piece to: If the specifies a , then there shall be a one-to-one correspondence between the set of s contained in that and the set of s contained in the of a unique constraint of the referenced table such that corresponding s are equivalent. Let referenced columns be the column or columns identified by that and let referenced column be one such column. Each referenced column shall identify a column of the referenced table and the same column shall not be identified more than once. I read the section on corresponding column names are equivalent to imply that (b,a) and (a,b) aren't equivalent for this purpose because the corresponding column names are different. That's a difference from SQL92's version which basically just says the sets are the same. Basically, it's a compliance point, and something that's necessary to make information_schema work for foreign key constraints because you can't seem to use information_schema to read how the columns line up without it because they didn't put an ordering column on the side storing the referenced keys. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] constraints and sql92 information_schema compliance
Stephan Szabo <[EMAIL PROTECTED]> writes: > The main options seem to be: > When we're allowing other order access, immediately reorder the > constraint information to match the primary key order. This helps out > with IS since the loaded constraint should display properly, but > theoretically could change the visual representation after load for people > who don't care about this option. > Change the representation unconditionally on dump. Basically reorder the > constraint at dump time to always generate a dump in SQL03 order. This has > the same downside as the above except only after another dump/restore. > Change the representation on dump only if the flag is set (probably > exporting this as an option to pg_dump as well). This could be a little > more difficult to use, but pretty much causes the user to drive the > choice. I'm missing something. On what basis do you claim that there's a "SQL03 order", ie some ordering mandated by the spec? What problem is this really solving? 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
Re: [HACKERS] [PATCHES] About the structure of WAL Files.
On 3/15/06, Charlie Wang <[EMAIL PROTECTED]> wrote: Could you tell me something about the internal structure of the WAL Files? Aside from looking at all the xlog code, the easiest way to understand the logs is to look at Tom's xlogdump utility. You can find it in the archives somewhere but it needs to be updated a little such as changing the CRC and rtree calls. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [HACKERS] About the structure of WAL Files.
Simon Riggs wrote: > On Wed, 2006-03-15 at 09:12 -0400, Alvaro Herrera wrote: > > Charlie Wang wrote: > > > I want to write a function to expose the content of WAL Files as streams, > > > and then send to somewhere else, record by record. > > > > Re: the WAL records, most likely they are useless outside the server > > that generated them, because they refer to relations using Oids, and to > > specific page addresses which are probably not going to be exactly the > > same anywhere else (consider VACUUM commands running at different > > times). > > Yet it should be pointed out that there is a commercial product that > says it uses this information to provide a replication system for > PostgreSQL, so presumably that problem can be solved... Are you referring to Mammoth Replicator? Let me point out that it doesn't really use the WAL. Now that I think of it, maybe the problem _can_ be solved. Consider e.g. the WAL-dispatcher resolving the relation Oids to relnames before shipping, and the WAL-receiver resolving that back to a local Oid before "applying". And it only needs to get the tuple contents; it can ignore the position, and it can resolve the indexing by itself. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] About the structure of WAL Files.
On Wed, 2006-03-15 at 09:12 -0400, Alvaro Herrera wrote: > Charlie Wang wrote: > > I want to write a function to expose the content of WAL Files as streams, > > and then send to somewhere else, record by record. > > Re: the WAL records, most likely they are useless outside the server > that generated them, because they refer to relations using Oids, and to > specific page addresses which are probably not going to be exactly the > same anywhere else (consider VACUUM commands running at different > times). Yet it should be pointed out that there is a commercial product that says it uses this information to provide a replication system for PostgreSQL, so presumably that problem can be solved... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] About the structure of WAL Files.
Charlie Wang wrote: > I want to write a function to expose the content of WAL Files as streams, > and then send to somewhere else, record by record. This is not a patch -- please do not write to the pgsql-patches list. Thanks. Re: the WAL records, most likely they are useless outside the server that generated them, because they refer to relations using Oids, and to specific page addresses which are probably not going to be exactly the same anywhere else (consider VACUUM commands running at different times). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Restoring a Full Cluster on a Different Architecture (32 x 64)
Well, actually we're ain't gonna do this procedure regularly, but just in case of failure - if it ever happens.For the moment, I did the dump/restore and it worked, but took almost 1 hour, due to tsearch2 indexes on a table. Yeah, I thought 64-bit data could be stored on other files than pg_control. So, there's only one way.Thanks for helping!2006/3/14, Jim C. Nasby < [EMAIL PROTECTED]>:On Tue, Mar 14, 2006 at 02:12:39PM -0500, Jonah H. Harris wrote: > On 3/14/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:> >> > Setting up Slony might be another option; you'd essentially be following> > the procedure used to speed up a PostgreSQL upgrade that would normally > > require a dump/reload.>>> If you need to do this on a continuing basis, Slony is the best way to go.> If it's a one-time shot, I'd just pipe pg_dump to a psql that's connected to > PostgreSQL on your 32-bit system.Well, it's not so much a matter of how often you have to do it, but whatkind of downtime you can tolerate. Setting up Slony just to move acluster from one machine to another is a good amount of extra work, so if you don't have the uptime requirement it probably doesn't make sense.--Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]Pervasive Software http://pervasive.comwork: 512-231-6117vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 -- Regards,Rodrigo HjortGTI - Projeto PostgreSQLCELEPAR - Cia de Informática do Paranáhttp://www.pr.gov.br
Re: [HACKERS] [PERFORM] Hanging queries on dual CPU windows
Magnus Hagander wrote: On Monday 13 March 2006 12:27, Magnus Hagander wrote: Great. That'll certainly help - now you don't have to wait for binaries from me. What I'd be interested in seeing is new stackdumps from a version where you: 1) Do *not* have the patch for mutexes applied 2) Have removed "static" from all the function devlarations in signal.c and socket.c, bnoth in src/backend/port/win32. I did that, and the interesting thing is that: 1. It takes much longer to hang. ?! That shouldn't be related :-) 2. Once it hangs, the stacktraces are the same. Hmm. That's weird :-( Did you do a make clean? Sometimes needed to get the port stuff in, mingw messes up sometimes. 3 (and this is the kicker). The thing starts working again after a couple (+/- 5) minutes ? Interesting. And you get nothing in the logs? (pg_log / eventlog) There's a report on -bugs ([BUGS] Random hang during commit / [EMAIL PROTECTED]) that might well be related to this. -- Richard Huxton Archonet Ltd ---(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
