Re: [HACKERS] [JDBC] Where are we on stored procedures?

additional rows to it. Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com signature.asc Description: OpenPGP digital signature

Re: [JDBC] [HACKERS] Where are we on stored procedures?

(touchedrows int, somethingelse datetime), setof (article int, description text) for a function/method that has two resultsets, one of those with always one row. markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888

Re: [HACKERS] [JDBC] Statement Timeout and Locking

Hi, Tom, Tom Lane schrieb: Anyway the short-term answer for Markus is don't do it that way. We ought to think about making the backend's behavior more consistent, though. I'll split the query into three. Having it in one query just was a convenience here. Thanks, Markus

Re: [HACKERS] [PERFORM] Bad n_distinct estimation; hacks suggested?

Hi, Josh, Josh Berkus wrote: Yes, actually. We need 3 different estimation methods: 1 for tables where we can sample a large % of pages (say, = 0.1) 1 for tables where we sample a small % of pages but are easily estimated 1 for tables which are not easily estimated by we can't afford to

Re: [HACKERS] qsort again (was Re: [PERFORM] Strange Create Index

pointers (names, URLs, papers) to such algorithms? Thanks a lot, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end

Re: [HACKERS] qsort again (was Re: [PERFORM] Strange Create Index

Hi, Mark, Mark Lewis schrieb: It seems that instead of maintaining a different sorting code path for each data type, you could get away with one generic path and one (hopefully faster) path if you allowed data types to optionally support a 'sortKey' interface by providing a function f which

Re: [HACKERS] qsort again (was Re: [PERFORM] Strange Create Index

Hi, David, David Lang schrieb: In SQL_ASCII, just take the first 4 characters (or 8, if using a 64-bit sortKey as elsewhere suggested). The sorting key doesn't need to be a one-to-one mapping. that would violate your second contraint ( f(a)==f(b) iff (a==b) ) no, it doesn't. When both

Re: [HACKERS] qsort again (was Re: [PERFORM] Strange Create

Hi, Ron, Ron schrieb: OK, so here's _a_ way (there are others) to obtain a mapping such that if a b then f(a) f (b) and if a == b then f(a) == f(b) Pretend each row is a integer of row size (so a 2KB row becomes a 16Kb integer; a 4KB row becomes a 32Kb integer; etc) Since even a 1TB

Re: [HACKERS] [PERFORM] Looking for a tool to * pg tables as ERDs

Hi, Ron, Ron Peacetree wrote: Where * == {print | save to PDF | save to mumble format | display on screen} Anyone know of one? psql with fancy output formatting comes to my mind, or COPY table TO file SQL command. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG

Re: [HACKERS] [PERFORM] Looking for a tool to * pg tables as ERDs

Hi, Andrew, Andrew Dunstan wrote: How on earth can either of these have to do with producing an ERD? Sorry, the ERD thing got lost in my mind while resolving the *. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against

Re: [HACKERS] [SQL] Interval subtracting

month 30 days', depending on the timestamp we apply the interval. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

functions have an implicit return parameter of int8. Parameters may be NULL when they are not known at query planning time. What do you think about this idea? The same scheme could be used to add a CPUCOST_ESTIMATOR to expensive functions. HTH, Markus -- Markus Schaber | Logical TrackingTracing

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

return ($2-$1)/$3, and for some functions even constant estimates will be good enough. - please execute me and store my results in a temporary storage, count the rows returned, and plan the outer query accordingly That's an interesting idea. Markus -- Markus Schaber | Logical TrackingTracing

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

solution would be to cache the result of the estimator function. Sophisticated estimator functions are free to use the pg_statistics views for their row count estimation. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against

Re: [HACKERS] large object regression tests

boundary case could be tested. Is there any precedent on where to grab such a large chunk of data from? You could generate such data on the fly, as part of the test scripts. E. G. a blob of zero bytes, blob of 0xff bytes, a blob of pseudo random data... Markus -- Markus Schaber | Logical

Re: [HACKERS] Fixed length data types issue

-- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature

Re: [HACKERS] Proposal for GUID datatype

Hi, Gevik, Gevik Babakhani wrote: typreceive = not supported typsend = not supported Any reason why you don't want to support binary transmissions? Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software

Re: [HACKERS] dump / restore functionality

really needs to be in the first part ... or at least there's no visible benefit to delaying loading it. I agree, it has to be in the first part, especially as data types and input functions needed for the table definitions and table data may be defined therein. HTH, Markus -- Markus Schaber

Re: [HACKERS] Getting a move on for 8.2 beta

, and read-only access to files used to do the build. I know vmware supports this, probably others too... A chroot / fakeroot combined with unionfs should do the same, probably with less effort. There are other user-mode jail projects that also block networking. Markus -- Markus Schaber

Re: [HACKERS] Fixed length data types issue

, and then see whether he'd need to detoast for condition checking and for actual data fetching. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org

Re: [HACKERS] [ADMIN] Vacuum error on database postgres

a transaction block then the ANALYZE is divided into multiple xacts and so doesn't try to hold locks on multiple tables concurrently. autovacuum won't try to do that either.) Is there any reason to allow ANALYZE run insinde a transaction at all? Markus -- Markus Schaber | Logical TrackingTracing

Re: [HACKERS] [ADMIN] Vacuum error on database postgres

commands in the transaction. OK, I see. But this leads to the danger that, should the transaction abort afterwards, we're left with borked stats, or are those rolled back accordingly? Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS

Re: [HACKERS] minor feature request: Secure defaults during

in the docs for CREATE FUNCTION, so users are informed about the security implications. [1] alternative spelling proposals: [NOT] PUBLIC or PUBLIC | PRIVATE Thinking about it, CREATE [OR REPLACE] [PUBLIC|PRIVATE] FUNCTION ... seems the most sexy variant in my eyes. HTH, Markus -- Markus Schaber

Re: [HACKERS] Truncation of email subject lines

. The only possible bug I could see is that your mailer implements the indentation incorrectly (tabs vs. spaces, incorrect level of indentation etc.). Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe

Re: [HACKERS] Truncation of email subject lines

Hi, Bruce, Markus Schaber wrote: Should I try hacking my mail reader to prevent this? I think I see where it is happening in the code. AFAICT, the wrapping of long header lines by indentation (as your mailer seems to do) is RFC conformant, so I think it is majordomo who needs the fix

Re: [pgsql-www] [HACKERS] Developer's Wiki

, that should help]. Maybe you should rename the public writable Wiki page list to Wishlist instead of Todo, to make the difference more explicit. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe

Re: [HACKERS] [PATCHES] Patch for UUID datatype (beta)

. Officially, it was a bug in the eeprom content generating software, but there were rumours that the manufacturer wanted to avoid paying the registration fees for the mac address ranges... Just gettin' off topic, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf

Re: [HACKERS] Index bloat problem in 7.4

, if it's not in the release notes, it is unlikely that it dit make it into the 7.4 releases. You may try backporting it yourself, or use a cronjob or such issuing regular REINDEX commands during the night (or whenever you have idle hours). HTH; Markus -- Markus Schaber | Logical TrackingTracing

Re: [HACKERS] Cause of moving-target FSM space-needed reports

MaxFSMPages pages with useful free space? +1 for both from my side, it has bitten me and our admins several times now. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org

Re: [HACKERS] Index bloat problem in 7.4

, as it _is_ in the release notes, this does not render my statement wrong, but pointless :-) Thanks for your correction, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org

Re: [HACKERS] PostgreSQL 8.2beta1 w/ VALUES

Hi, Luke, Luke Lonergan wrote: If it's going to roll back the entire load after that one warning, it should terminate there. AFAIK, a warning is no reason for PostgreSQL to roll back anything. That's the difference between a warning and an error. HTH, Markus -- Markus Schaber | Logical

Re: [HACKERS] pgsql: We're going to have to spell dotless i

Hi, Hannu, Hannu Krosing wrote: Are you sure it's UCS-4 ? I've always thought that XML is what is given in xml tag, and utf-8 if no charset is given. You have to distinguish between the supported charset, and the document encoding. HTH, Markus -- Markus Schaber | Logical TrackingTracing

Re: [HACKERS] pgsql: We're going to have to spell dotless i

is Unicode, giving us the possibility to use entities; to use non-latin1 characters. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc

Re: [HACKERS] pgsql: We're going to have to spell dotless i

, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature

[HACKERS] DELETE RETURNING

it work in 8.3. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature

[HACKERS] Small docu mismatch

not to mention this option. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature

Re: [HACKERS] DELETE RETURNING

, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature

Re: [HACKERS] DROP FUNCTION IF EXISTS

. I have no idea if this is a problem of psql or some other problem, and it was not annoying enough to report it... I think this could be a problem with libreadline / libedit not being utf8-safe. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software

Re: [HACKERS] Release Notes: Major Changes in 8.2

Hi, Bruce, Bruce Momjian wrote: listitem para Allow inheritance to be removed from tables /para /listitem I'd enhance that to Allow table inheritance relationships to be defined for and removed from pre-existing tables. HTH, Markus -- Markus Schaber

Re: [HACKERS] Release Notes: Major Changes in 8.2

pre-existing tables Agree, that's excellent. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description

Re: [HACKERS] Internal Transaction

Hi, Marlon, Marlon Petry wrote: would like to know postgres implements ACID ? has some document ? http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html HTH, Schabi -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight

Re: [HACKERS] jar in repository

, it was part of the archive file when I unpacked, and I forgot to ignore it when checking in. It's removed now, together with a clarification in the README. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents

Ignore that mail (was: [HACKERS] jar in repository)

Hi, Markus Schaber wrote: [something about a postgis.jar] Please ignore that mail, it got to the wrong list. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org

Re: [HACKERS] Faster StrNCpy

: It may be that the 64-byte range crosses a page boundary. Now guess what happens when this next page is not mapped - segfault. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org

Re: [HACKERS] Backup and restore through JDBC

of the database. :-) HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature

[HACKERS] send()/receive() and on-disk storage

() are only used for client communication in the V3 protocol binary mode, and COPY in binary mode, but the backend stores them in the tables as_is (modulo compression/ TOASTing). Is my assumption correct? Btw, I'm going to triple-check this in the source and via tests, too. Thanks, Markus -- Markus

Re: [HACKERS] send()/receive() and on-disk storage

Hi, Martijn, Martijn van Oosterhout wrote: It would be terribly inefficient to call those functions for each read/write. The disk has the internal format, send/receive deal with a portable not-host-dependant representation of the data. Thanks. Markus -- Markus Schaber | Logical

Re: [HACKERS] Backup and restore through JDBC

that. That's taking evil and being *creative* with it. :) Well, combine that with some firewall / hosts.allow rules, and sslwrap with certificate based 2-way authentication, if you insist on security. :-) Keep on lauging, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl

[HACKERS] timestamptz alias

Hi, I'm happy that the rather verbose timestamp with time zone has the much nicer alias timestamptz, however it seems that this alias is not documented, neither at http://developer.postgresql.org/pgdocs/postgres/datatype-datetime.html nor at

Re: [HACKERS] timestamptz alias

+ to the SQL standard, and thus are more portable. But there are no + plans to drop the short aliases in future versions. +/para + /note + para typetime/type, typetimestamp/type, and typeinterval/type accept an optional precision value HTH, Markus -- Markus Schaber

Re: [HACKERS] timestamptz alias

the user which of the names are the ones to avoid, and the reasons to do so. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc

Re: [HACKERS] tsearch2 error msg

in the query (e. G. by joining different tables), so that will help further. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description

Re: [HACKERS] timestamptz alias

might be a reason. Using pure timestamp may lead human readers to be confused, because it has both meanings with and without timezone historically, this might be a reason to prefer the timestamp with[out] time zone wording. HTH, Markus -- Markus Schaber | Logical TrackingTracing International

Re: [HACKERS] PL/pgSQL Todo, better information in errcontext from

it impossible to have stable regression-test outputs. I'm not really convinced that we need more than the function name. I also think that the OID will hurt here, but schema and argument types may be useful in some corner cases. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl

Re: [HACKERS] pg_dump exclusion switches and functions/types

. Is that OK? Sounds fine. Is there a possibility to dump only those objects? Maybe --large-objects and --languages? Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org

Re: [HACKERS] Upgrading a database dump/restore

enough bandwith and disk space to copy the pg directory, they also have enough to create and store a bzip2 compressed dump of the database. Or did I miss something? HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against

Re: [HACKERS] Interface of the R-tree in order to work with postgresql

with spatial access methods, like the R-tree, I need to find a .h or .c to begin reading the code, so can you tell me where to start? Besides the hints others posted here, you might want to look at contrib/tsearch2 and PostGIS for examples of how to use GIST. HTH, Markus -- Markus Schaber

Re: [HACKERS] query optimization with UDFs

of the tables. Has that idea been considered? HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature

Re: [HACKERS] GROUP BY on a large table -- an idea

-- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature

Re: [HACKERS] SQL functions, INSERT/UPDATE/DELETE RETURNING, and

the tuplestore, for large resultsets? Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature

Re: [HACKERS] Postgresql Caching

Hi, Shane, Shane Ambler wrote: CREATE TABLESPACE myramcache LOCATION MEMORY(2GB); It's already possible to do this, just create the TABLESPACE in a ramdisk / tmpfs or whatever is available for your OS. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf

Re: [HACKERS] postgres database crashed

to assume that it's something like a wrong length flag in some corner case in your code. ... Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc

Re: [HACKERS] postgres database crashed

are among the basics of SQL, they're pretty well tested. Could you try to replace your image type e. G. with bytea for your test purposes, and see, whether it crashes, too? HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against

Re: [HACKERS] Syntax bug? Group by?

speaking, need to group by that column? Try: SELECT 15 as ycis_id, min(tindex), avt(tindex) from y where ycis_id = 15; HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org

Re: [HACKERS] Syntax bug? Group by?

make the parser to special case the = operator, and maybe some others, however I doubt it's worth the effort. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org

Re: [HACKERS] Syntax bug? Group by?

by to get ycis_id in the results. And, as I wrote, this is only possible when the query parser special-cases the = operator (compared to all other operators). HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software

Re: [HACKERS] bug or feature, || -operator and NULLs

. Heh, well, c is supposed to be not NULL. Missed that. I was using the equals to include (NULL = NULL) but in SQL it's not like that. Maybe you should replace = with IS NOT DISTINCT FROM :-) HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software

Re: [HACKERS] Multiple postmaster + RPM + locale issues

and they are installed correctly). Did you look at how the debian guys handle this? HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description

Re: [HACKERS] xlogdump fixups and WAL log question.

of the checkpoint record since we could no longer rely on the XLogRecPtr being a byte offset within the file. pg_WAL_filter could correct the XLogRecPtr and file sizes during the filter run. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS

Re: [DOCS] [HACKERS] Replication documentation addition

solutions of his problem. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature