Re: [GENERAL] LISTEN / NOTIFY
On Fri, Dec 08, 2006 at 03:38:49PM +0100, Hannes Dorbath wrote: > Is there a way I can have notifications to be streamed to the listener, > so I don't need to poll with LISTEN? > > LISTEN foo; > LISTEN > NOTIFY foo; > NOTIFY > Asynchronous notification "foo" received from server process with PID 3593. > > This does work for the same backend, but not for notifications issued > from another one. AIUI they are, it's just that the client needs to be looking for incoming data. If you're using psql for example, I don't beleive it checks for incoming data until you send a command. How you actually acheive that in your code is a seperate question and depends on the language you're using. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] loading data, creating indexes, clustering, vacuum...
On Thu, Dec 07, 2006 at 02:52:11PM -0800, Alan Hodgson wrote: > On Thursday 07 December 2006 08:38, "Angva" <[EMAIL PROTECTED]> wrote: > > three commands. For instance I have a hunch that creating the indexes > > first (as I do now) could slow down the clustering - perhaps the row > > locations in the indexes all have to be updated as the cluster command > > shifts their locations? And perhaps vacuuming should be done before > > clustering so that dead tuples aren't "in the way"? > > clustering also removes the dead tuples. > > I would just: > > - create one index, the one to be clustered > - cluster the table > - create the remaining indexes And then run ANALYSE. No need to vacuum because the cluster did that already. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Internal function call from C-language function
On Thu, Dec 07, 2006 at 06:01:13PM +0100, Zoltan Boszormenyi wrote: > >I have just one more question: > >How can I get an Oid out of a Datum, i.e. > >how do I know what type I get in a given Datum? > >DatumGetObjectId() seems to give me an Oid that > >was specifically stored as a Datum. > > I have found the alternative solution. > If t is HeapTupleHeader then: There is no way to tell what type is in a Datum, it's just that, nothing else. The information about the actual type can come from elsewhere, for example: - If extracting from a tuple, the tuple descriptor has the type (as you found) - If passed as argument, the fcinfo struct *may* have the type information - The SPI interface provide ways to get information also On the other side, a Datum is abstract, and you can receive a Datum as argument and pass it to other functions without needing to know what type it is. But you better so it right because there is no type checking on that level. As for the backtrace, you can get gdb to attach to the backend after you connect. Then when you get the segfault, gdb will catch it and show you exactly where. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Internal function call from C-language function
On Thu, Dec 07, 2006 at 01:40:22PM +0100, Zoltan Boszormenyi wrote:
> text_in() doesn't exists, it's textin() but I have to call it through
> DirectFunctionCall1(), like this:
>
> yeardatum = DirectFunctionCall1(textin, CStringGetDatum("year"));
>
> However, the session crashes on the subsequent
>
> returndatum = DirectFunctionCall2(timestamp_part, yeardatum, timest);
It would be a good idea to actually find out where it crashes, that
would help you work out what the actual problem is. Just looking at the
code you posted, I only see this other bit that looks a bit suspect:
Datum timest;
boolisnull;
t = PG_GETARG_HEAPTUPLEHEADER(0);
timest = DatumGetTimestamp(GetAttributeByName(t, "ts_today", &isnull));
You're calling DatumGetTimestamp() which would return a timestamp
(probably some structure) but you're storing it in a Datum. Just take
the result of GetAttributeByName directly.
Get at least a backtrace next time it crashes...
Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] Internal function call from C-language function
On Thu, Dec 07, 2006 at 12:55:47PM +0100, Zoltan Boszormenyi wrote:
> However, I have another problem. I have this in the code:
>yeardatum = CStringGetDatum("year");
>elog(NOTICE, "CStringGetDatum() 1 OK");
>returndatum = DirectFunctionCall2(timestamp_part, yeardatum,
> timest);
You're passing a cstring as first argument, whereas I'm fairly sure you
should be passing text. When calling from C the're no argument
checking. I think what you're looking for is:
yeardatum = text_in("year");
Or something like that.
Hope this helps,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] Internal function call from C-language function
On Thu, Dec 07, 2006 at 09:48:25AM +0100, Zoltan Boszormenyi wrote:
> Hi,
>
> I need to call date_part() from a C function.
> How to do that?
Look in fmgr.h for the functions {Oid,Direct,}FunctionCall* which
provide various ways to call other functions.
There's also FunctionCallInvoke() which is more efficient if you're
going to call it lots of times.
Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] HELP: Urgent, Vacuum problem
On Wed, Dec 06, 2006 at 10:45:08AM -0500, Schwenker, Stephen wrote: > ... I know it's not > the best solution but I couldn't get an answer fast enough because every > time I send an email to the list, I get a message saying it was stalled > and I have to wait for it to be approved by the moderator. I don't know > why. :| You need to subscribe. If you don't actually want to receive list mail, you configure your email address "nomail". Then you can send messages and they'll get through straight away. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] [PERFORM] Locking in PostgreSQL?
On Wed, Dec 06, 2006 at 08:26:45AM -0500, Dave Cramer wrote: > >>Unless you specifically ask for it postgresql doesn't lock any > >>rows when you update data. > >> > >Thats not right. UPDATE will force a RowExclusiveLock to rows > >matching the WHERE clause, or all if no one is specified. > > Apparently I've completely misunderstood MVCC then My > understanding is that unless you do a select ... for update then > update the rows will not be locked . I think it comes down to what you mean by RowExclusiveLock. In MVCC, writers don't block readers, so even if someone executes an update on a row, readers (SELECT statements) will not be blocked. So it's not a lock as such, more a "I've updated this row, go find the new version if that's appropriate for your snapshot". Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] PG Admin
On Tue, Dec 05, 2006 at 07:55:35AM -0800, Bob Pawley wrote: > Perhaps I can - it will be learning curve for me. However, the development > would be so much easier to apply if it were available in PostgreSQL in a > form similar to generating a serial column. Your assertion that it would be easy is incorrect. If what you were asking was easy, it would have been done long ago... Sequences without holes is nowhere near as trivial as people think it is. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] pgsql bug found?
On Mon, Dec 04, 2006 at 06:52:19AM -0800, Ronin wrote: > Hi when I do the following function it fills 2 dates per day from 1970 > to 2050, except that some months (typical 2 months per year) have 4 > dates for one day. this is totally freaky.. I wonder if postgresql is > tripping over itself making a double entry every now and again. > > for instance I constantly get the following entries It's either a wierd daylight savings thing, or something to do with the fact that not all months have 31 days. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Unsuccessful SIGINT - More Info
On Fri, Dec 01, 2006 at 08:26:53PM -0700, Brian Wipf wrote: > Now I know the cause at least. If anyone has an idea on how to kill a > similar hung connection without rebooting the server, I would > appreciate any suggestions. I'm unsure about why it wouldn't respond to a sigint, but did you try try stronger signals? Have a ncie day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] DBI module for postgres 1.4.3
On Fri, Dec 01, 2006 at 02:49:59PM -0500, Jasbinder Singh Bali wrote: > Hi > I'm using postgresql version 1.4.3. No such version exists. What exactly do you mean? > Trying to connect to it throught perl code. > Just wondering if DBI would be the best tool to use to accomplish this task. > Which version of DBI should I be using. Whichever version is installed by your system should be fine. Clients are compatable across many versions. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Ident authentication failed for user "dsivam"
On Tue, Nov 28, 2006 at 03:40:09PM -0800, [EMAIL PROTECTED] wrote: > Hi- > > I've run into a problem using Postgres 8.1 that has me stumped: > > I'm logged on as user 'postgres' and I can connect using the following > command: > > bash$>psql > > However when I specify the host: > > bash$>psql -h localhost Maybe you're not running an ident daemon? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] How to increace nightly backup speed
On Wed, Nov 29, 2006 at 11:21:41PM +0100, Bernhard Weisshuhn wrote: > LZO is pretty much rock solid. It is used in OpenVPN and supposedly was > used for the communication with NASAs Mars Rovers Spirit and > Opportunity, if that counts as trusted. It's also GPL, which makes it a hard sell. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] coalesce with all nulls can only be assigned to
On Wed, Nov 29, 2006 at 01:45:09PM -0600, Kevin Grittner wrote: > > And do what? The only information you have is that all the inputs > > are of unknown type. > > I know this is naive, but, what is the type information of the bare > null? Could that be used? A null can be of any type, string, text, integer, etc. If you have a bare null in a query, it gets type "unknown" and the system has to guess. Looking up possible matching operators and functions can help, but if none of those possibilites help, it gets assigned type "text". That's why as soon as one entry has a type, it works because the system can assume the others are of the same type. Compare this with pointers in C. There you can have a char ponter and and an integer pointer, both NULL yet they cannot be used interchangably, they are of different types. I'm curious how in such a strongly typed language as Java you represent a null without any associated type. Or does Java not distinguish either? Does this help? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] backend crash following load command
On Tue, Nov 28, 2006 at 04:09:11PM -0500, Tom Lane wrote: > The mmap man page is pretty vague on the subject, but I wonder whether > the shlib isn't effectively treated as copy-on-write --- that is, any > attempted overwrite of the file happens only after the mmap region has > been fully copied. Without that, it'd be impossible to update core > shared libraries like libc.so without a system reboot, but Linux doesn't > seem to need that. Hmm? To upgrade libc.so you merely need to delete the old one and install the new one, there's no need to preserve the inode. The mmap() is private, but no, Linux does not keep a backup copy of the shared library if you overwrite it. The behaviour of overwriting the backing store of a private mapping is explicitly undefined. I did some digging. At one point there was protection for overwriting shared libraries, you could pass MAP_DENYWRITE to mmap(), which would cause any writes to the file to fail with ETXTBSY, just like it does for normal executables. However: MAP_DENYWRITE This flag is ignored. (Long ago, it signalled that attempts to write to the underlying file should fail with ETXTBUSY. But this was a source of denial-of-service attacks.) > I suspect that this issue is specific to dlsym() and has nothing to do > with the safeness of ordinary usage of a shared library. The reason > 8.2 is getting bit is that it tries to do a dlsym() lookup during shlib > unload, which we never did before. (Merlin, I assume you have been > doing the same things with 8.1 and before without a problem?) I wouldn't be surprised if this were the problem. People testing shared libraries would probably not be testing what happened between the time the shared-library was overwritten and the LOAD command was reexecuted. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] backend crash following load command
On Tue, Nov 28, 2006 at 03:23:36PM -0500, Tom Lane wrote:
> I'd suggest putting together a simple stand-alone test case and filing
> a bug report against glibc. You probably just need
>
> dlopen(...);
> system("cp -f over the .so file");
> dlsym(...);
How can glibc do anything about this? dlopen() mmaps the .so into
memory and the cp overwrites what was mmaped, changing what is in
memory.
Ideally, the cp should fail with ETXTBSY, but that doesn't happen, so
what else can you do?
Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] backend crash following load command
On Tue, Nov 28, 2006 at 02:38:18PM -0500, Merlin Moncure wrote: > On 11/28/06, Tom Lane <[EMAIL PROTECTED]> wrote: > >"Merlin Moncure" <[EMAIL PROTECTED]> writes: > >> We are getting a backend crash after issueing a load command. > > > >No crash from your example here (on Fedora Core 5). What platform and > >gcc are you using exactly? Can you provide a stack trace from the crash? > > ok, an update on this. we actually covered up the bug in reducing the > problem to our test case. our make system used cp -f to overwite the > .so file in use by postgresql. interestingly, this will cause a crash > on the .so reload via LOAD. There may be a perfectly normal reason > for this. Err, that means copy is just rewriting the executable code in the backend of the server, while it's running, which understandably crashes. Probably while trying to unload the old library. I suppose the answer is: don't do that. The protection of ETXTBUSY only applies to code started via exec(). Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Datafiles binary portable?
On Tue, Nov 28, 2006 at 11:00:35AM -0500, Stephen Harris wrote: > This is probably a silly question, but are the database files binary portable? > eg could I take datafiles from a Sparc and copy them to an Intel machine, > or would the endianness differences kill me? No. It may not even be compatable across the same platform with different compilers and/or configure flags. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] FK pointing to a VIEW
On Tue, Nov 28, 2006 at 03:33:54PM +0200, Olexandr Melnyk wrote: > >This would mean something like an index spreading over more then one > >table in the end, or did I miss something ? > > Yes. But that is hardly implementable. Actually, an index over multiple tables is not really the hard part. It's setting it up so you don't cause deadlocks that's tricky. And what people really want is *unique* indexes over multiple tables, but there the locking considerations are even worse. My gut feeling is that it actually won't be that bad once someone hits on the right idea and codes it up, but I've been known to be wrong before. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Very minor "configure" issue?
On Tue, Nov 28, 2006 at 11:11:26AM -0500, Stephen Harris wrote: > The solution, obviously, is LDFLAGS=-Wl,-R/opt/mystuff/lib ./configure > and now everything configures and builds cleanly, but it might be nice > for that to be automatic. RPATH is evil. If you're going to install libraries in non-standard paths, at least include those paths in the system-wide library search path or add them to LD_LIBRARY_PATH. You could even symlink the libs into /usr/lib, that would also solve the problem. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] How to increace nightly backup speed
On Tue, Nov 28, 2006 at 03:29:44PM +0200, Andrus wrote: > > If you've got excess CPU capacity at night, I wonder if -Z1 or -Z2 > > would speed the backup since it reduces the amount of data written > > to disk. > > Where to find study which pg_dump compares backup speed and backup size by > using various -Z options ? pg_dump uses zlib, you should be able to find statistic on that. > I'm wondering by -Z9 increases backup speed. > Info-zip zip.exe -9 config option does not decrease compiression speed I don't beleive that. The whole point of increasing the compression level is that it spends longer on the data to compress it better. It you could compress it better in the same time, you'd just do it and not make it optional. > I'm really wondering why -Z9 decreases backup speed significantly. Level 9 is the absolute maximum compression level, it tries really hard to make the data small, but it's usually not that much better than level 6. If you're transferring over a 100Mb network, I wouldn't recommend going higher than level 3. It is not unheard of that increasing the compression makes the process take longer, because you're saturating the CPU while your network is idle. Also, if you have a multicore system, it may be worth having pg_dump not compress but piping the output through gzip, then you can use both processors simultaneously. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] IN clause
On Mon, Nov 27, 2006 at 05:24:31PM -0600, Jim Nasby wrote: > In any case, like Oracle, PostgreSQL does not index NULL values (at > least not in btree). Actually, PostgreSQL does store NULL values in an index, otherwise you could never use them for full index scans (think multicolumn indexes). You can't use the index for IS NULL tests, although patches exist for that. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] which version? old user coming back....
On Sat, Nov 25, 2006 at 01:38:17PM -0800, [EMAIL PROTECTED] wrote: > > Look into Debian unstable, or if you are partial to Red Hat-derived > > stuff look into Fedora. > > I have and if it is there I can't find it. Where are you looking? http://packages.debian.org/unstable/misc/postgresql-7.4 http://packages.debian.org/unstable/misc/postgresql-8.0 http://packages.debian.org/unstable/misc/postgresql-8.1 http://packages.debian.org/experimental/misc/postgresql-8.2 Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] fatal error on 8.1 server
On Mon, Nov 27, 2006 at 02:22:19PM -0600, Tony Caduto wrote: > Just in case anyone is interested I did get it up and running with no > damage to the system. > It took well over a year for it to reach the 1 million threshold mark. You mean one *billion*, right? That's one busy server! Hopeefully you've updated your maintainence setup to avoid this in the future? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] indexes
On Sat, Nov 25, 2006 at 12:14:11PM -0500, Tom Allison wrote: > If the real-world primary key is large (say up to 100 characters in length) > then the disadvantage is that you are duplicating this referenced key in > several other tables, each element taking up 100 characters. Space is > wasted when compared to int4 ID's. But not really sure if this is a > performance problem for SELECT except for the space required (varchar(128) > vs. int4). Well, it kinda sucks for joining because comparing strings may take tens to hundreds of times as long as compairng integers. It's not just byte-wise comparison but you have to be locale sensetive about it. My main problem with using any kind of string as "natural key" is that once you start passing it around you have worry about the encoding of said string and when it goes a round-trip to a client, will what you get back still be the same? If you can guarentee ASCII you might be ok, but otherwise... Integers have none of these issues. > Having two keys, a primary_key of int4 and a unique key of varchar(128) > would be very ugly on INSERT/DELETE/UPDATE queries because of the index > overhead. In general my tables are queried several orders of magnitude more often than they are updated, so index update cost isn't all that relevent. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] IN clause
On Fri, Nov 24, 2006 at 05:31:07PM +0530, surabhi.ahuja wrote: > That is fine > but what I was actually expecting is this > if > select * from table where col_name in (null, 'a', 'b'); > > to return those rows where col_name is null or if it = a or if it is = b > > But i think in does not not support null queries , am i right? You'll need to check the standard, but IN() treats NULL specially, I think it returns NULL if any of the elements is null, or something like that. It certainly doesn't work the way you think it does. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] COPY FROM : out of memory
On Thu, Nov 23, 2006 at 11:27:06AM -0500, Tom Lane wrote: > Arnaud Lesauvage <[EMAIL PROTECTED]> writes: > > When trying to import a 20M rows csv file into PostgreSQL, I > > get : > > > ERROR: out of memory > > État SQL :53200 > > Détail :Failed on request of size 1073741823. > > Contexte : COPY tmp, line 1 > > Can you put together a self-contained example? The reference to "line > 1" suggests that you wouldn't need the whole 20M row file, just the > first few rows ... Maybe it's a line termination problem? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?
On Thu, Nov 23, 2006 at 10:23:55AM -0600, Ron Johnson wrote: > For those times when and that when numeric sequences *are* needed > (employee_id and account_number for example) they should include a > check digit, to ensure that you don't mis-type a number and charge > the wrong account. Sure, but the check digit does not need to be stored, as it can be regenerated on demand. The user interface just verifies the check digit, then throws it away. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] log database changes - a design problem?
On Thu, Nov 23, 2006 at 08:53:28AM +0100, Luca Ferrari wrote: > Hi all, > I've got my database on which I'm building a Java application. Since I'd like > to store information about who inserted and updated a specified record, I > placed in my main tables a few additional fields to store such information: > Here comes my question: how can I catch user changes to each record in the > database without be bored with user/date details? Anyone can suggest me a > smart solution and/or database design? Anyone has already found such kind of > problem? Sounds like something a trigger would be good for. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] MSSQL to PostgreSQL : Encoding problem
On Wed, Nov 22, 2006 at 01:55:55PM -0500, Brandon Aiken wrote: > Gee, didn't Unicode just so simplify this codepage mess? Remember > when it was just ASCII, EBCDIC, ANSI, and localized codepages? I think that's one reason why Unix has standardised on UTF-8 rather than one of the other Unicode variants. For transmission between systems it's the easiest to get right... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Buffer overflow in psql
On Wed, Nov 22, 2006 at 11:11:09AM -0500, Jack Orenstein wrote:
> I'm using Postgrseql 7.4.8. In January, I reported a psql bug. The
> problem was that an INSERT issued through psql would cause a
> crash. There was no problem with other operations I tried, or with the
> same INSERT submitted through JDBC. The discussion thread begins here:
> http://archives.postgresql.org/pgsql-bugs/2006-01/msg00071.php
> case PGRES_COMMAND_OK:
> {
> charbuf[10];
>
> success = true;
> sprintf(buf, "%u", (unsigned int)
> PQoidValue(results));
>
> In 8.1.5, the sprintf is replaced by an snprintf, resulting in a less
> serious form of the bug.
Looks like you found something.
> 1) Is one of the postgresql developers willing to get this fix into
>the next release? (We're patching our own 7.4.8 build.)
Probably, though I don't know the release cycle for backpatches.
> 2) If no one else has hit this, then it suggests I might be in
>uncharted territory with OIDs getting this high. Do I need to
>review my vacuuming strategy? (I can summarize my vacuuming
>strategy for anyone interested.)
I think most people have OIDs disabled, which avoids the problem
entirely. Perhaps that's why it hasn't been run into before.
Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] User-defined-type in C crashing PostgreSQL server: What am I doing wrong?
On Sat, Nov 18, 2006 at 01:07:15PM -0800, J. Greg Davidson wrote: > Hello, > > My user-defined types are crashing the PostgreSQL server and I don't > understand why. I've been trying to figure it out on my own for overr > a week. I've cooked what I'm doing down to the essentials and I'm > asking for help. Help: What am I doing wrong? This may be a long shot but: > CREATE TYPE pair ( > INTERNALLENGTH = 4, -- 32-bits > INPUT = pair_in, > OUTPUT = pair_out > ); You're not specifying PASSEDBYVALUE, so I think postgres is assuming you're returning a *pointer* to 4 bytes, so it's dying trying to copy it. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Data conversion tools?
On Thu, Nov 16, 2006 at 06:23:23PM -0700, Jim Nasby wrote: > Well, for tables it wouldn't be hard to craft a query that spits out > the appropriate ALTER TABLE RENAME statements. Unfortunately, ALTER > TABLE doesn't support renaming columns, but it might be safe to run > an update on the system tables to accomplish that. Sorry? # \h alter table ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_column ALTER TABLE name RENAME TO new_name Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Linux hard drive/device nodes for a Postgres RAID array
On Thu, Nov 16, 2006 at 12:40:41PM -0800, Glen Parker wrote: > But now, pull the drive from port 2 and boot the system. You will now > have SDA,SDB,SDC. The kernel will now fail BOTH of the last two drives > from the RAID array. The one that was SDC is gone, and obviously fails. > The one that was SDD is now SDC, so its ID doesn't match what the > kernel thought it should be, so it fails it too. If you kill the FIRST > drive in the array, I believe the entire array becomes inoperable > because of the resulting shift and ID mismatch. Is that really so? AIUI the position of the disk in the array is stored on the disk itself, so it should be able to handle disks moving around no problem, have you tried it? > So the question is, is there some way to "pin" a drive to a device > mapping? In other words, is there a way to force the drive on port 0 to > always be SDA, and the drive on port 2 to always be SDC, even if the > drive on port 1 fails or is pulled? I thought you could do this with options on the command-line, or using udev. But I don't think it's actually necessary. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Accessing postgres in perl app using ssl authentication
On Thu, Nov 16, 2006 at 02:24:43PM -0500, beer wrote: > Hello > > I know this isnt specifically a postgres question, but people here > are much more likely to have an answer than on a perl list. I have > an app in perl that uses DBD::Pg to access the database. This module > does not seem to have support for using certificate authentication. > I'm hoping someone here might be aware of a module that does support > this option. DBD::Pg uses the libpq library to make the connection and so should have all the same capabilites as any C program. Have you tried setting it up for certificate authentication? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Using SAN Splits to instantly copy a DB
On Wed, Nov 15, 2006 at 03:25:38PM -0600, Ron Johnson wrote: > However, what if the WAL is not on the SAN? You'd have to shut down > pg anyway, in order to copy the WAL to a new directory, no? You have to copy the *entire* cluster, you cannot split out one database, for example. Two postmaster instances cannot share data, period. > Lastly: in order to do SAN splitting without risking your data, > wouldn't you have to configure the disks as RAID-15 (mirrored > RAID-5), since splitting a RAID10 would leave you with stripesets? As long as you get all the data it OK. I'm not 100% clear on what SAN splitting is so I'm not totally sure. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Using SAN Splits to instantly copy a DB
> How does it know what a crashed PostgreSQL database look like? > > Besides, active transactions need to be *rolled back*, not written > ahead, since half the data hasn't been sent from the computer yet. There's a section of the docs dealing with this: http://www.postgresql.org/docs/8.1/static/wal.html Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Using SAN Splits to instantly copy a DB
On Wed, Nov 15, 2006 at 01:41:47PM -0600, Ron Johnson wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 11/15/06 09:47, Jim Nasby wrote: > > On Nov 14, 2006, at 3:44 PM, Paul Silveira wrote: > [snip] > > Rule 2 is needed to ensure that the data files in the database are all > > consistent to each other. If you have a SAN/filesystem with snapshot > > capability (sounds like you do), then you can do that to create the copy > > rather than shutting the database down. > > How does SAN-snapshot ensure transactional consistency? There is write-ahead logging to do that. It's the same machanism used to ensure database consistancy after a crash. When you take a snapshot and start a new postmaster on the snapshot, it sees what looks like a crashed database and recovers it to the instant it snapshotted (aka "crashed"). Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] PgSQL not recognized
On Tue, Nov 14, 2006 at 12:28:33PM -0800, [EMAIL PROTECTED] wrote: > Obviously not as good I should have. I missed the begin and the end, > adding those solved the problem. I am new to Postgres and it is quite > differently designed compared to what I have been using so far, I just > thought I could step right into it and do as I used to. I'm not sure where you're coming from but AIUI plpgsql is modelled on the oracle language. I've never used oracle like that so can't really comment how that compares to any other database. > How do I ignore the error? The problem is that I would like to have a > set of drop/create statements executed at once and one drop failing > will abort the script execution. That is why I was searching for some > conditional execution statement within the query. Did you mean using > begin/exception for this or were you just talking about > one-statement-per-query execution and "forgetting" about any possible > exceptions? Yes, I was thinking of begin/exception. You just make the exception do nothing. I've never really done this though, I've never had dymanic schema's that way. > Nonetheless, can you use PL/PgSQL without building functions? No, people suggest it from time to time, but I don't beleive anyone has submitted a complete proposal or patch for it. > This is > what I have been used to while using other SQL implementations. Not > related to the above problem - if a function can be built, then it can > accept the catalog, schema and table name and check whether the table > really exists using information_schema.tables. Just wondering, though, > is this something that Postgres is not designed for or am I missing > something that disallows me to run PL/PgSQL statements within "normal" > queries? You're not missing anything, the only language the database understands directly is SQL, other languages are restricted to use within functions. The documentation is large. There has been some effort to get it into a form more accessable, but it's hard when there's that much of it. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] PG_MODULE_MAGIC check in 8.2
On Tue, Nov 14, 2006 at 04:29:05PM -0800, Shelby Cain wrote: > You are assuming I have the source for the library and although I've > asked nicely via email Microsoft hasn't responded to my requests for > the source code to kernel32.dll. ;) I see, you're doing something rather unsupported... > Regardless, even if I had the source, adding PG_MODULE_MAGIC to an > arbitrary dll that has no need to know anything about the internals > of Postgresql comes at a cost of much more than a one line to the > source. It requires having all the random bits of developer > headers/libraries/etc from Postgresql which has been been properly > set up to work with my particular compiler environment. In this > case, my initial attempts to wrap the functions in a shared library > that I can control using the headers supplied with the beta3 > installer have met with little success under cygwin using -mno-cygwin > (which, in theory, should invoke mingw) and MSVC due to strange > desires to include things like strings.h, libintl.h and various other > files that don't exist. I can't remember exactly, but if you search the source for the error message you'll find the code. That said, do you have to specify the library kernel32? If you use language "internal", doesn't that find it since it's already loaded? Then you don't need any module magic at all... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] PG_MODULE_MAGIC check in 8.2
On Tue, Nov 14, 2006 at 02:46:41PM -0800, Shelby Cain wrote: > Could someone give me some general hints as to what modifications I'd > need to make in the 8.2 source tree in order to remove the > PG_MODULE_MAGIC requirement for loading shared libraries into the > backend? Is there any chance this could be made into a configurable > option so the user can choose the desired behavior? Why? It's a one line addition to the source of the module, much less work than trying to disable the check in the backend... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] The old Insert and retrieving your Serial problem in VB
On Tue, Nov 14, 2006 at 03:54:42PM -0600, John McCawley wrote: > I am once again dealing with that age old problem of retrieving the > value of your inserted serial field in VB. I am fully aware that I can > manually pull the currval or nextval from my sequence, but I consider > having to manually deal with an auto-generated database object an > *extremely* inelegant solution. Maybe you're looking for lastval()? http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] help needed, PG 8.0.0 ERROR: index is not a btree is solved in 8.0.9
On Tue, Nov 14, 2006 at 01:58:49PM +0530, surabhi.ahuja wrote: > hi > I am using Postgres 8.0.0 and we found this issue "ERROR: index > "patient_pkey" is not a btree" > I have been informed that we should shift to Postgres 8.0.9 I beleive you've asked this a few times already. As a rule it always best to run the latest minor release, because bugs do get fixed, that's why there are minor releases. Do you get this often? Because a reindex fixes it, obviously. > I have some questions regarding this: > 1.will this vesion solve the problem that I have mentioned? No idea. > 2. If we install postgres 8.1.5 instead of Postgres 8.0.0 I ll have to build > my c++ application again right? I don't think so. I can't imagine why. The client libraries work across several server versions. > 3. I am currently using postgresql-8.0-310.jdbc3.jar, for java > applications. Would I have to change this jar as well? and if yes > where can I find it? That's for the backend, right? Or not? If it's for the client end you shouldn't need to change that either. > 4. the most important question is : Is there any test case that you ran in > order to confirm that the above issue will not occur with PG 8.1.5. I need > this test case that I can run in order to propose that we should upgrade to > 8.1.5 Do you have a test-case that causes it to happen? That's the only test there is. > 5. Can you please provide a link to this Bug. I want to just see the proble, > resolution, verification of this bug. I don't think there's been a bug relating to this. In just about every case confirmed, it's been memory corruption or disk corruption. As someone put it: This [error] says that one of two fields that should never change, in fixed positions in the first block of a btree index, didn't have the right values. No-one has come across an bug that would cause that. Basically, for more info we need to see the hexdump of the first 8k of the index file to see why the system thinks it's not a btree. Can you do that? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] PgSQL not recognized
On Mon, Nov 13, 2006 at 03:08:18PM -0800, [EMAIL PROTECTED] wrote: > I just installed a fresh Postgres database. select version(); gives: > > "PostgreSQL 8.1.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) > 3.4.2 (mingw-special)" > > Normal statements like select * from sometable work fine. Have you read the documentation for the creation of functions? And looked at the examples? http://www.postgresql.org/docs/8.1/static/plpgsql.html > I tried using the functions: > > create function izitest() returns void as > 'if 1 <> 1 then >select 1 > else >select 2 > end if;' language 'plpgsql' > > where plpgsql is the name of the language I created. This gave the same > error: plpgsql is a language like oracles, you must have a BEGIN/END at least. There's also the simpler 'sql' language, but it has no IF statement. BTW, your statement is equivalent to: SELECT CASE WHEN 1 <> 1 THEN 1 ELSE 2 END; > Anyway, to pose another question about this. The reason I need the > above is to check whether the table exists (from > information_schema.tables) and drop it only in that case (dirty > exception handling). There might be a way to do it (in 8.1) in another > way then using ifs to check. The other question still stands, however. In the latest release, in beta, you can DROP IF EXISTS. However, most people just execute the drop and ignore the error, less risk of race conditions. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] libpq.so full name?
On Tue, Nov 14, 2006 at 07:54:30PM +0530, surabhi.ahuja wrote: > hi > I had Postgres 8.0.0 installed on my m/c and > now i have installed Postgresql 8.1.4 (FC 4) x86 , 64 bit arch. > > i rebuild my executable giving -L$(ROOT)/postgres/lib/ -lpq > > the directory $(ROOT)/postgres/lib > contains libpq.so with size = 133320 > > it gives an error (linking error i think), > howver if i rename the above file to libpq.so.3 then it works fine. > What is this behaviour? Please provide the exact commandline you're running and the exact error message. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Out of memory (Failed on request size 24)
On Tue, Nov 14, 2006 at 05:53:08AM -0500, Rob Owen wrote: > PostgreSQL 8.0.3 running on AIX 5.3 (same thing happens on 5.1 though). > DBMS was running fine for some months but now one of the databases isn't > accessible. Any help would be greatly appreciated. > > DBMS starts up fine, but any operation on the files database (psql files, > vaccumdb files, pgdump files) yields the same result. The client responds with > > > psql files Something screwed up: > TopTransactionContext: 2145378304 total in 266 blocks; 928 free (14 chunks); > 2145377376 used That's a lot of memory. I thought there was a check on negative sized allocations... Did "make check" pass ok? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] encoding advice requested
On Tue, Nov 14, 2006 at 12:01:44AM +0100, Daniel Verite wrote: > Also, you'll find this extensively and better explained in this article, for > example: > http://ppewww.ph.gla.ac.uk/~flavell/charset/form-i18n.html This is a *really* good article about character sets and form submission. Especially the tip about how to get the browser to tell you what encoding it used. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Using PL/pgSQL
On Mon, Nov 13, 2006 at 01:26:08PM -0800, Kojak wrote: > I'm trying to install PL/pgSQL so that I can use it in a FUNCTION. I'm > using PostGres 6.5.x (I know I need to upgrade...but that is another > subject...). No, you need to upgrade first. 6.5 is positivly prehistoric, no-one is going to be much help dealing with something that old. It will leak memory and other bizarre errors like that. That said: > ERROR: Load of file /usr/local/pgsql/lib/plpgsql.so failed: dlopen > '/usr/local/pgsql/lib/plpgsql.so' failed. (Shared object"libpq.so.2" > not found) > > I have verified that these 2 files do exist in the > /usr/local/pgsql/lib/ directory. Their permissions are both rw-r--r-- > and owned by pgsql:pgsql Maybe that directory is not in the search path? In any case, plpgsql should not be using that library anyway, so there is something very odd with your installation. Perhaps you should upgrade. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Table and Field namestyle best practices?
On Sun, Nov 12, 2006 at 02:21:10PM -0800, novnov wrote: > > OK, thanks. I'm having a major internal debate about how I'm going to adjust > my habits to pgsql's 'lowercase is simplest' reality, all of this is > helpful. Well, it's more like "no quoting is simplest", then all the identifiers are case-insensetive and you don't have to worry much about upper and lower case. You will read them back in lowercase though. But to answer your question, unquoted identifiers can contain letters, numbers and the underscore. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Why isn't it allowed to create an index in a schema other than public?
On Sun, Nov 12, 2006 at 01:38:30PM -0200, Jorge Godoy wrote: > Chris Mair <[EMAIL PROTECTED]> writes: > > > Just say > > create index testing123_index on testing.testing123 (otherthing); > > and you'll otain exactly what you want (see below). > > > > Bye, Chris. > > I know I can workaround such debilitation. What I wanted to know is if > there's some reason (such as performance gain, for example) for that > decision. I think his point was that the index is always in the same schema as the table itself. It states this quite clearly in the documentation. So what exactly is the debilitation? It seems to be doing exactly what you want. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] encoding advice requested
On Sat, Nov 11, 2006 at 02:45:00PM -0500, Rick Schumeyer wrote: > My database locale is en_US, and by default my databases are UTF8. > > My application code allows the user to paste text into a box and submit > it to the database. Sometimes the pasted text contains non UTF8 > characters, typically the "fancy" forms of quotes and apostrophes. The > database does not appreciate it when the application attempts to store > these characters. What encoding does your application use? Whatever that is, that's what you should be using in your SET client_encoding statement. Note you can set that variable per user also. > b) I can issue a SET CLIENT_ENCODING TO 'LATIN1'; statement every time I > open a connection. A brief test indicates this will work. This is the solution, assuming ofcourse your application is in Latin1. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] wildcard alias
I don't know what your query or aliases have to do with joins, but if you want to join two tables on all fields with the same name, you can do a NATURAL JOIN. Have a nice day, On Fri, Nov 10, 2006 at 05:17:12PM -0500, Matthew Terenzio wrote: > > I suppose I should have named things differently but is there a way to > join two tables with a bunch of identical column names and rather than > explicitly alias each column just use some sort of wildcard like: > > SELECT tablename.* AS alias.* > > OR do I have to name each column like: > > columnname as alias > > Thanks much. > > > ---(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 -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Which DatumGet ?
On Fri, Nov 10, 2006 at 05:30:09PM +0100, Enrico wrote: > In my table I have a field myfied defined by > > myfield numeric(20,5); > > Which kind of DatumGet macro I have to use? As an indirect type you want DatumGetPointer and cast it to numeric*. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] ECPG and multiple threads
On Fri, Nov 10, 2006 at 12:23:19AM -0800, Adam wrote: > Hi, > > I'm porting an embedded-SQL application to PostgreSQL so embedded sql > is a must for me. My application is multi-threaded. SQL queries are > used by several threads. The program keeps terminating unexpectedly in > deemingly "innocent" places. Note, you might be able to make ECPG and libpq thread-safe, however even then you *cannot* use the same connection simultaneously from different threads. You will have to open a new connection for each parallel connection you want to handle. Or use locking. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Can non-superuser install c functions ?
On Thu, Nov 09, 2006 at 04:04:33PM -0500, Rick Schumeyer wrote: > I am transferring a database from a system where I am a pg superuser to > one where I am not. > results in: permission denied for language c > > Do I need to get the pg administrator to install my database ? Yes. Letting someone install C language functions is equivalent to giving then access to all data in the database and possibly most of the rest of the system. So it's restricted. So yes, the pg superuser in charge of the cluster needs to install tsearch2 for you. The rest you can do yourself. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Datum problem
On Thu, Nov 09, 2006 at 06:12:08PM +0100, Enrico wrote:
> Hi,
> I'm newbie of programming postgresql sever side,
> I write the function below and when I execute
>
> SELECT (anag_art, 150) AS esistenza from anag_art order by 1;
>
> result is something about this:
Well, you didn't actually call any function, so it just returned the
two parameters as you asked... You have other problems though.
> visualizza_esistenza(PG_FUNCTION_ARGS)
> {
> HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
> int16limit = PG_GETARG_INT16(1);
An "integer" is not an "int16", so this is wrong, you want INT32
> bool isnull;
> Datum esistenza;
> int16 es;
>
> esistenza = GetAttributeByName(t, "esistenza", &isnull);
> es = DatumGetInt16(esistenza);
Possibly same problem here.
> if (esistenza > limit)
> PG_RETURN_INT16(es);
You declared you were going to return an int4, yet you're returing
something else.
> }
What are you returning if the if statement doesn't get run?
Hope this helps,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] 8.1.2 postmaster died
On Thu, Nov 09, 2006 at 11:52:48AM +0500, Shoaib Mir wrote: > Have a look at "16.4.3. Linux Memory Overcommit" on > http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html If you look at the beginning of the thread you'dve seen we're talking about HPUX here... Maybe they have an OOM killer too? Although maybe it's quotas? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] converting Informix outer to Postgres
On Tue, Nov 07, 2006 at 06:35:05PM -0500, [EMAIL PROTECTED] wrote: > Hi all, > I have been working on this Informix SQL query which has an outer join. > I have attached Informix query and my "supposedly" solution to this query > but I cannot get the same count. I appreciate for any help. > Thanks. I don't know what the Informix outer join is, but is it like the SQL FULL OUTER JOIN? Have you tried using that? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] X.50x OID representation
On Tue, Nov 07, 2006 at 03:57:43AM -0800, redhog wrote: > I need to save something very similar to X.50x IOD:s, that is, paths > where the path-components are numbers. For example: 10.5.5003.24.35. Looks like something for ltree, which is a datatype for storing tree-structured data. http://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/contrib/ltree/README.ltree Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Header meaning for pg_dump
On Tue, Nov 07, 2006 at 08:40:54AM +0700, Premsun Choltanwanich wrote: > Dear Richard, > > Regarding the information you give to me, I understand that this > information is a thing that normally used by PostgreSQL system. And > the information seem to be placed on a comment area. So, Who need to > have a clearly understanding on the header information? AFAIK nothing (in the main distribution) actually uses it. It's more a header for people while they're scanning the dump. It also makes a nice anchor point for sed scripts if you want to extract a single object from the dump. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] timezone difference in timestamp?
On Mon, Nov 06, 2006 at 03:30:35PM +, meltedown wrote: > Short version: I'm trying to turn a unix timestamp into a psql > timestamp, but there is a 5 hour difference. Is this because of > timezones ? Can I just subtract 5 hours to get the right value ? Not sure what you're using, but by my calculations postgresql is correct. $ perl -e 'print scalar(gmtime(1162789200)),"\n"' Mon Nov 6 05:00:00 2006 Make sure you understand whether the dates your comparing are in the same timezone. Maybe you want 'timestamp with time zone'. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Simple stored procedure examples?
On Sun, Nov 05, 2006 at 08:51:52AM -0800, novnov wrote: > I would really prefer it if simple names like Item and ItemName not be > double quoted. You're saying that postgres itself would only require double > quotes if the table was originally decribed that way (and it is, being > created by pgAdmin). Seems like an odd mismatch between pgsql and pgAdmin, > why would pgAdmin take the seemingly uneccessary step of double quoting > names like Item? Evidently, pgAdmin sees you using uppercase letters and decides to quote them. It could also not quote them, then it would be case insensetive. If you create the table with lowercase, does pgadmin allow you to refer to them with mixed case? I don't use pgAdmin, so I can't really say much about this. > Any suggestions for a db admin tool that does not introduce this error? I don't use pgAdmin, the only tool I use is psql, and it doesn't automatically quote anything, ever. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Simple stored procedure examples?
On Sat, Nov 04, 2006 at 08:35:28AM -0800, novnov wrote:
> So pgSQL is case sensitive and that include keywords like UPDATE and SET.
No it's not. Only identifiers in double quotes (") are case-sensetive.
So, in your example below, because the function was created with double
quotes, you now have to use double quotes and the same case every time
you want to use it. If you create a function/table/column without
double quotes, you never need quotes and it is case-insensetive.
In your case it's possible that pgAdmin is adding the quotes for you,
maybe?
> There what worked, for the record:
>
> -- Function: "proc_UpdateItemName"()
>
> -- DROP FUNCTION "proc_UpdateItemName"();
>
> CREATE OR REPLACE FUNCTION "proc_UpdateItemName"()
> RETURNS void AS
> $BODY$UPDATE "Item" SET "ItemName" = 'fox';$BODY$
> LANGUAGE 'sql' VOLATILE;
> ALTER FUNCTION "proc_UpdateItemName"() OWNER TO postgres;
>
Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] dividing integers not producing decimal fractions
On Fri, Nov 03, 2006 at 02:03:59PM -0800, [EMAIL PROTECTED] wrote: > You're right (I dug around in the documentation and edjoocated myself). > However: > sales=# select 1/2::float; > ?column? > -- > 0.5 > (1 row) Note that in this case the "float" cast only applies to the last number. That's why you get this: > sales=# select (1/2)*4::float; > ?column? > -- > 0 > (1 row) The integer divide happens first. It is best to apply the cast to the first element of the expression, as expressions are parsed left-to-right, so: select (1::float/2)::4; Works better. However, mostly it's better to explicitly make all your constants non-integer if that's what you mean. This statement: select (1.0/2.0)*4.0; Gives the same result, but doesn't need any casts. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] explosion of tiny tables representing multiple
On Fri, Nov 03, 2006 at 08:25:25PM +, Benjamin Weaver wrote: > Dear Martijn, > > Wow, didn't know about arrays. Did lots of sql, but, as I think about it, > that was 7 years ago, and we didn't know about arrays then > > Are their performance problems with arrays? We will not likely be working > with more than 50,000 - 100,000 records. If by records you mean rows in the database, then 50,000 rows is a baby database, nothing to worry about there. Performence of arrays scale about linear with the number of elements in the array. So if most of your arrays have only 2 or 3 elements, the performence should be good. If you make a single array with 50,000 element, it's going to suck very badly. Note, recent versions of postgres have better support for arrays, including for indexing thereof. Especially the new GIN index type may be useful for you. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] UNICODE and UTF-8
On Sat, Nov 04, 2006 at 04:23:02PM +0100, Alain Roger wrote: > however, when i do this, my encoding is in UTF-8 via phpAdmin. > UTF8 is a part of UNICODE, but as i'm not sure on how many bits is UNICODE, > how can i setup my local DB to UNICODE value as my provider has ? As far a postgres is concerned, UTF8 is UNICODE. IIRC some versions said one name, some the other, but they mean the same thing. So maybe you have a different version than your provider? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] explosion of tiny tables representing multiple fields--Is this necessary?
On Thu, Nov 02, 2006 at 04:36:49PM +, Benjamin Weaver wrote: > Dear PostGreSQL experts, > > > I am working with text objects. A text object will have lots of fields that > are potentially multiple. There may be more than one author, more than one > modern editor, more than one edition number, etc. Have you considered using arrays? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] fetching unique pins in a high-transaction environment...
On Sun, Oct 29, 2006 at 08:32:12AM -0800, Bobus wrote: > 10 users request a pin at the same time. What is the easiest/best way > to ensure that the 10 users will get 10 unique pins, while eliminating > any waiting? What are you doing that holds locks for so long? If you do a select for update, take the first row, update and commit, you should be able to handle dozens of those per second. In any case, another approach I've seen is to divide the list into several. For example, make your query do a: select for update where pin > 'X' where X is a random number between 0 and 9. That cuts the amount of contention dramatically, so you can use the simple method. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Invoking java in a trigger
On Fri, Nov 03, 2006 at 03:22:40PM -, David Potts wrote: > Is it possible to invoke a computer language other than postgres sql as > the result of a trigger firing, eg something like Java. Sure, many languages are supported, C, perl, tcl, python and yes, even Java. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Isolation / Visibility inside a trigger
On Fri, Nov 03, 2006 at 10:24:21AM -0300, Jorge Godoy wrote: > But then, the answer to my question is that even inside the same transaction > or receiving the NEW row those functions called by the trigger shouldn't see > the information. Did I get it right? Correct. Before triggers happen prior to the backend even attampting to insert. AIUI it happens before uniqueness checks, check constraints, foreign key checks, etc. As far as anything else in the system is concerned, the row does not exist yet. If it's just the flag field that's being updated, perhaps you could split the flags and result field into a seperate table updated by the after trigger. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Isolation / Visibility inside a trigger
On Fri, Nov 03, 2006 at 09:49:17AM -0300, Jorge Godoy wrote: > I'm trying to fix a bug (?) in my design but I'd like to understand my mistake > first, so that I don't do that again. > But when I converted those to (before) triggers I started having a problem > where it tries reading data from the soon-to-be-commited row but the functions > called can't read it, even though the serial column has already been > incremented and the insert command issued. "Before" triggers can't see the data changes yet, they are, by definition, before the commit. From what you write it doesn't look like you really need to change the row being written, so you could just as well use "after" trigger, which don't have this problem... > - shouldn't the data be available inside the transaction and visible for > all operations called by the trigger? > > - shouldn't I use before triggers when manipulating data and changing > values (since after triggers ignore results)? Before trigger are only needed if you want to alter the row being committed. Both before and after triggers can alter *other* data in the database. Maybe you need to split the triggers into tasks done before (updating fields in NEW) and tasks after (updating other tables). Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Counting records in a PL/pgsql cursor
On Thu, Nov 02, 2006 at 10:43:58PM +0100, Magnus Hagander wrote: > Is there any way to count how many hits I got in a cursor in PL/pgsql? > > I have a function that will "window" through the result of a (large) > query based on two parameters, but I also want to return the number of > hits to the client. Right now I'm looping through the entire cursor and > incrementing a local variable, which I later return (along with the > first records in the resultset) to the client. But this seems > horribly inefficient... I'd just like to ask "how many rows are in this > cursor", is there a way to do that without looping through them all? You can move to the end, look at the row number, then move to the beginning. It will still need to materialise the entire resultset though. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] start up and shut down script
On Fri, Nov 03, 2006 at 01:40:35PM +0530, surabhi.ahuja wrote: > I am using PostgreSQL 8.0.0. > > i have noted the following lines in the script to start or shut down > postmaster. > Can you please let me know why they are needed Where did you get these fragments from? They appear to be for a redhat based system, not sure because I don't have them. > if [ "`uname`" = "Linux" ]; then >INITD=/etc/rc.d/init.d >. $INITD/functions ># Get config. > . /etc/sysconfig/network > fi Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] lots of values for IN() clause
On Thu, Nov 02, 2006 at 05:16:40PM +0100, Markus Schiltknecht wrote: > OTOH, having to configure such things is not exactly user friendly. I > guess it's difficult to determine the stack limit in a cross-platform > way. Or does having that configuration option other reasons for existence? A patch went in recently that (on platforms where it's possible) tries to determine the maximum stack depth and complains if you set it too large. So this problem should go away in the future... There might be situations where you want to be able to use a larger stack, that's why it's configurable. PostgreSQL now actually checks whether the number you provided makes sense. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] pg_proc Question
On Thu, Nov 02, 2006 at 09:57:28AM -0500, Oisin Glynn wrote: > I have named all my functions in a pretty unique way and would like to > export just my functions. Is there any way to move just these functions > from one db to another.has anyone got a clever piece of SQL that spits > out ddl from a select on pg_proc? Or could I use the copy command on > pg_proc to spit my functions to file and then copy them into the new db? > My issue is that I have allot of dblink and other things I do not want > or need in the db and do not want to propagate this any further. I would say the easiest is to use pg_dump and then filter the output. It's simple enough for sed I think: pg_dump database | sed -ne '/^CREATE FUNCTION tag/,/^--/p' works well here... Replace tag with a pattern matching your functions. If you're using pl/pgsql you may need to do something more clever with the end marker... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] compiling c-function on various version
On Thu, Nov 02, 2006 at 10:53:52PM +0900, Hitoshi Harada wrote: > but couldn't, PG_VERSION is string type. > any other macro or definitions?? In recent versions there's a PG_VERSION_NUM. For older versions you can track changes in the catalog version number. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Weird double single quote issue
On Thu, Nov 02, 2006 at 01:35:41PM +0200, Peter wrote: > It is like the weirdest thing ever... > > I have a proc that dynamically generates SQL, executes it and returns > results as setof record. Some of fields are strings with single quotes > inside them. Since these strings are being picked up from database I > store them as: Not quite sure, but maybe some users are using inline parameters and some are using out of line parameters? The quoting rules only apply to inline paramaters. In any case, it appears someone is screwing up the quoting somewhere... Note that there are quoting functions to help create dynamic sql safely. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Encoding, Unicode, locales, etc.
On Wed, Nov 01, 2006 at 11:41:43AM +0100, Karsten Hilbert wrote: > Could this paragraph be put into the docs and/or the FAQ, > please ? Along with the recommendation that if you require > multiple encodings for your databases you better had your OS > locale configured properly for UTF8 and use UNICODE > databases or do initdb with the C-locale. Err, multiple encodings don't work full-stop. Any particular locale (as defined by POSIX) is only really designed to work with one encoding. The fact that the C locale produces an order when sorting UTF8 text is really just luck. In hindsight the people in POSIX who decided to tie locale and encoding into one variable should probably be shot, but it's a bit late now. > > This stuff is certainly far from ideal, but the amount of work involved > > to fix it is daunting; see many past pg-hackers discussions. > > Here are a few data points from my Debian/Testing system in > favour of not worrying too much about installed ICU size as > it is being used by other packages anyways: We'd need a suitable patch first before we start worrying about that. I think diskspace is less of an issue now. There are discussions going on about having the clog and the xlog taking dozens of megabytes. At the end of the day I don't think 10MB for the Unicode data it going to be that big a deal, *if* the patch solves all the problems in this area in a reasonably clean way... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] access and security
On Mon, Oct 30, 2006 at 01:34:34PM +0100, Andrew Kelly wrote: > Hi all, > > please forgive a (likely) less than clever question. > > Are the barriers provided by pg_hba.conf enough from a security > standpoint, or is it best to put up some iptable rules duplicating the > restrictions? iptables covers the entire server, whereas pg_hba.conf cancontrol per database. Think of it as layers. If you know only two other machines will ever access this server, you can use iptables to enforce this. From those two machines, you than use pg_hba.conf to fine-tune the access controls. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] what is the default username password for PostgreSQL, which was installed with FC5.
On Fri, Oct 27, 2006 at 11:43:26AM +0200, A. Kretschmer wrote: > am Fri, dem 27.10.2006, um 15:01:09 +0530 mailte Purusothaman A folgendes: > > Thanks for your immediate reply. > > > > I created new role "db1" and database "db1". > > > > but I cannot login using > > psql -Udb1 -ddb1 -hlocalhost > > results > > Fatal Error : Ident authentication failed for user "db1". > > You arn't the user called 'db1'? > > Either 'su - db1' and then psql, or change the settings in your > pg_hba.conf for this database and localhost to 'trust'. Right. As pointed out, the default authentication method "ident" means that the database user and the system user are linked, so system user "db1" can login as database user "db1" without a password. "trust" means anyone can login an anyone, which may or may not be what you want. You can define ident maps, where you state that system user "x" can login without password as database user "y". Check the documentation. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] what is the default username password for PostgreSQL, which was installed with FC5.
On Fri, Oct 27, 2006 at 02:45:57PM +0530, Purusothaman A wrote: > Hi all, > > I am using a Fedora Core 5 system. > I want to know what is the default username password for PostgreSQL, which > was installed with FC5. I'm not sure if there is one. However, usually you su to the postgres user which will let you in without a password. There you use "createuser" to make a user for yourself. Note, it really depends how the distributor has set the system up. Allowing the "postgres" user password-less access is common because it allows cronjobs as user "postgres" (backups for example) to run without any extra configuration. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] How to get joins to work
On Tue, Oct 24, 2006 at 02:43:07PM -0700, Bill Ewing wrote: > I am having trouble getting joins to work. In a Java app that uses Hibernate > 3.1, I am able to build queries that join two, three or more tables using > combinations of INNER JOIN, LEFT JOIN or RIGHT JOIN. But, I need FULL OUTER > JOIN to work and have not been able to get them to work in Hibernate. > > So I decided to go back to basics and practice trial joins in the PgAdminIII > Query tool (v1.4.1, Dec 05). > > Just to warm up, I did the following simple queries which all worked: > select * FROM rack r > select * FROM sample s > > The above two tables are linked. But, none of the following SQL worked: > select * FROM rack r JOIN sample s > select * FROM rack r INNER JOIN sample s These statements are incomplete. You need to say what you're joining on. For example: select * FROM rack r JOIN sample s USING (joinfield) or select * FROM rack r JOIN sample s ON (r.a = s.b); If you really don't want any constraints, use a comma, or a cross join Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] recovery from xid wraparound
On Tue, Oct 24, 2006 at 04:18:09PM +0100, Shane Wright wrote: > If I change vacuum_mem I'll need to at least 'pg_ctl reload' - will > it apply straightaway with the next vacuum query or does it need a > full restart? You can control it per session I think. So you can start psql and type: # set vacuum_mem=; SET # VACUUM VERBOSE; You don't have to change the main config, unless you want it to apply forever. Although, 8MB is small in general so you might want to up it anyway. But for this one-off vacuum of this large table you could give a much larger amount of memory. > Does vacuum_mem need shared memory? (i.e. is it subject to the OS's > limit) - have looked in the docs and googled but can't see detail on > this It's just ordinary memory. If you have a few gig to spare, you can give it all to the vacuum. > If I have managed to vacuum all the catalog tables, and my script has > ensured all user tables other than this one have been vacuumed, > then... will the first pass of vacuum on this have set the xid to > FrozenXID for all rows - i.e. is the table safe? Pass. Although I think the point is that it hasn't scanned to whole table yet because it ran out of memory... > Is it safe to say that if the catalog tables are ok and an individual > tables has been vacuumed then its data is safe? Yes... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] recovery from xid wraparound
On Tue, Oct 24, 2006 at 03:47:52PM +0100, Shane Wright wrote: > > Incidentally, how many passes of a table can vacuum make! Its currently > on its third trip through the 20Gb of indices, meaning another 7 hours > till completion [of this table]!. > > Assume it only does three passes? (it chooses based on the table > continuing to be updated while vacuum is running) It depends on how many tuples it needs to process and how much memory you gave it (the maintainence_work_mem settings). The more memory you give it, the less passes it needs to do... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] recovery from xid wraparound
On Tue, Oct 24, 2006 at 11:06:01AM +0100, Shane Wright wrote: > If I was to abort this vacuum, given that all other tables are vacuumed > (including system catalog tables), what's the worst case scenario? - > given that more transactions are happening on the database Only tables that havn't been vacuumed in the last billion transactions are at risk. It's possible that if you've vacuumed that large table recently by itself that all the data is actually safe, just the system doesn't realise it. Just make sure you've really covered *all* the system tables. If they go you get really wierd results. > If I understand correctly, it would be that some rows could disappear > from this large unvacuumed table if their xid was too old - but no other > consequence? The VACUUM would make them reappear. To truly disappear they would have to be 3 billion transactions old. That leaves the unique index issue I mentioned. > (fully aware that a db-wide vacuum is needed, but if it can [safely] > wait for the weekend that would be preferable) That's risk-management. For example, all the really old tuples are possibly near the beginning of the table, thus this current vacuum will have fixed them already. But to get a handle on that you need to analyse your tuple turnover and usage ratio. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] recovery from xid wraparound
On Tue, Oct 24, 2006 at 07:43:15AM +0100, Shane Wright wrote: > Anyway - not noticed any data loss yet and was hoping it would be such > that if all tables had been vacuumed recently (including system catalog > tables), that there would be no remaining rows that would appear to > have a future xid and so the database should be ok? Running vacuum is the right solution, but I think you have to let it finish. In particular, in that version a database-wide vacuum has to complete before it will update the datfrozenxid (it's not tracked per table). > a) is my assumption about the database being ok correct - assuming all > tables have been vacuumed recently, including catalog tables? Should be ok, but apparently you missed one, or didn't do a database wide vacuum. > b) is it possible to safely abort my whole table vacuum now so I can > run it at the weekend when there's less traffic? Aborting vacuum is safe, but you have to do a database-wide vacuum at some point. > c) if I have experienced data loss, on the assumption all the table > structure remains (looks like it does), and I have a working backup > from before the xid wraparound (I do), can I just reinsert any > detected-missing data at the application level without needing a > dump/reload? A VACUUM will recover any data that slipped beyond the horizon less than 1 billion transactions ago, which I think covers you completely. The only issue is that unique indexes may be confused because new conflicting data may have been inserted while the old data was invisible. Only you can say if that's going to be an issue. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] storage size of "bitstring"?
On Sun, Oct 22, 2006 at 06:24:43PM +0200, Alex Mayrhofer wrote: > Hi, > > Two short questions: > > 1) What storage size does the "bit(n)" data type have? Is it one byte per 8 > bits (plus a "length" byte)? I didn't find this in neither the docs nor the > list archives ... It'll be a varlena structure, see backend/utils/adt/varbit.c. So 4 bytes + space needed for bits. > 2) Additionally, how much storage space does a functional index which > returns "boolean" approx. take? Will it be a bitmap? A boolean is one byte, plus whatever overhead is associated with the index. > Reason: i need to add some flags to a row, and i don't want to spend an > entire byte per flag on a "boolean" column (even worse, new flags could > probably be added in the future) - would it make sense to use > a bitstring, together with a functional index on any "interesting" bit in > that case? I'd say give it a shot. Seems reasonable. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] How to find out about zlib compression
On Sun, Oct 22, 2006 at 09:55:23AM +0200, Thomas Pundt wrote: > On Sunday 22 October 2006 09:43, Low Kian Seong wrote: > | It says in the postgresql 8 documentation : > | > | "If PostgreSQL was built on a system with the zlib compression library > | installed, the custom dump format will compress data as it writes it to the > | output file" > | > | My question, if we are using binaries on a rpm based system and without > | looking at the src.rpm is there a way to query the postgresql server to > | find out whether it was built against the zlib compression library ? > > ldd /path/to/your/bin/postgres | grep libz You should be checking the libraries used by pg_dump, not postgres. It's pg_dump that does the compression, the actual server does not use an external compression library (directly anyway). Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] PostgreSQL, LGPL and GPL.
On Fri, Oct 20, 2006 at 03:35:34PM -0500, Ron Johnson wrote: > Exactly. The "Linus View" is that dynamic linking and "socket > conversations" are *not* linking in the GPL2 meaning, but the FSF & > RMS think differently. The GPL3 seems to codify that strictness. Dynamic linking may be an issue, but talking over a socket doesn't create any kind of dependancy at all. I don't think anyone has ever tried to claim that talking to a GPL server requires your code to be GPL also. The existing counterexamples alone... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] why not kill -9 postmaster
On Sat, Oct 21, 2006 at 12:20:35AM +0930, Shane Ambler wrote: > If you kill -9 the postmaster the system can still finish sending > changes to disk and close the file but pulling the power cord can stop a > write in the middle of a block giving you half new data and half old > data in the one file. Well, if you kill -9 the postmaster all the connections stay alive and stay processing tuples and writing to disk, except the coordination is gone. Some queues won't be processed, some signals will be ignored, if the postmaster pid gets reused you'll have some fun. In particular, the sinval-queue processing would break, which could lead to some interesting issues. But I expect any number of issues to start occurring. A half-written disk blocks is a solved problem, postgresql will recover from that without blinking. > It's all a matter of timing. Pulling the plug is *way* safer, it's a known quantity. As Tom said, killing the postmaster needs cleanup, and some people screwup the cleanup enough to corrupt their own data. Now: killall -9 postgres (kill the parents, all the clients, autovacuum, bgwriter, etc) all in one go is much more like a crash. But that's not what's being discussed here. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] VACUUM and open transactions
On Thu, Oct 19, 2006 at 04:25:09PM -0400, Joseph S wrote: > >The problem is that the "old" transaction can see effects of later > >started transactions, so VACUUM can't delete the later stuff either... > > How can it see effects of transactions that started after it? Check the documentation for the difference the READ COMMITTED and SERIALIZABLE transaction. The former (the default) will see the results of any committed transactions, even if they started later. http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html#XACT-READ-COMMITTED Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] VACUUM and open transactions
On Thu, Oct 19, 2006 at 04:18:50PM -0400, Joseph Shraibman wrote: > I'm running postgres 8.0.8. I have a table that is updated very > rapidly, so I vacuum it every 10 minutes. The problem is that I > sometimes have transactions that hang out for a long time without doing > anything. These transactions are preventing VACUUM from cleaning up > tuples that were created and then deleted in transactions that started > way after the hanging one. Is there any way to fix this? Sure, don't keep transactions open for so long. Is there a particular reason you do that? The problem is that the "old" transaction can see effects of later started transactions, so VACUUM can't delete the later stuff either... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] COPY FROM STDIN instead of INSERT
On Wed, Oct 18, 2006 at 04:20:41PM +0400, Ilja Golshtein wrote: > And my question remains. > Is it Ok to use COPY BINARY FROM STDIN instead of multiple INSERTs? The reason why copy is faster is because it doesn't have to parse/plan/execute all the queries. In exchange you can't use expressions or joins to fill the table, only raw data. Binary may be slightly faster because the datum parsing can be partially skipped, but that's hardly much benefit over a text copy. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] How to _really_use a non-default tablespace
On Tue, Oct 17, 2006 at 05:54:42PM +0200, Andras Simon wrote: > >The xlog is not split by database, all databases share the same xlog. > > OK, I see. The question then is how far does it grow. If its size is > comparable to that of the actual data, then having separate > tablespaces is not as useful as it first seemed to me. But I might be > missing something. xlogs are recycled. You can control the growth somewhat by playing with the xlog settings in the config. It should stabilise at about 16MB times the wal segments. Have a nice dat, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] How to _really_use a non-default tablespace
On Tue, Oct 17, 2006 at 04:56:09PM +0200, Andras Simon wrote: > As a more drastic example: I created a DB on a tablespace TS, and > copied a 25 MB file into one of its tables. I ended up having 60 > MB of extra data in $PGDATA/pg_xlog that doesn't go away even > after dropping the database. The xlog is not split by database, all databases share the same xlog. Have a ncie day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] datatype preceded by underscore creates array
On Mon, Oct 16, 2006 at 01:16:34PM -0700, George Pavlov wrote: > Hmm, I am not sure I particularly like this behavior or the "ignore it" > advice. Suppose someone makes a typo in his/her table definition: meant > to create an int4 column but accidentally typed an underscore. You'd > expect the statement to fail. Instead it doesn't fail but creates an > unexpected datatype for the column. If undescore is a purposeful (rather > than an accidental) SQL standard extension one would expect it to be (a) > documented in some place like > http://www.postgresql.org/docs/8.1/static/arrays.html#AEN5584, and (b) > behaving more consistently (if _foo is a synonym for foo[] then all > variations of "foo" should support it). Hmm, if someone typos to get "int8" instead of "int4" they get the wrong datatype too, I don't know if that's an argument. The reason is that all types need to have an identifier. I suppose they could be called "pg_internal_array_type_for_int4", but for historical reasons it's just _int4. It's deprecated, you're not encouraged to use it and if it were easy to get rid of it would have been done a long time ago. > Not sure what you mean--char(x) is not an oddity and it does have array > support: The types char, integer, real, etc are the SQL names for the types and they have special SQL incantations for them. The underlying types are actually called bpchar, int4 and float8. The latter have magic array types, the former don't. > create table a7 (b char(1)[]); > CREATE TABLE This is just another way of saying: create table a7 (b _bpchar) except that doesn't allow you to specify a length... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] datatype preceded by underscore creates array
On Mon, Oct 16, 2006 at 12:33:35PM -0700, George Pavlov wrote: > Is there any special meaning to preceding a datatype (or at least some > datatypes) in a table or function definition by underscore that is a > synonym for an array? I can't see it documented anywhere. Below are some > examples. The other question is why "_int4" parses to int[], but "_int" > does not, etc. This is on PostgreSQL 8.1.3 Linux. Yep, the array type is represented internally by prefixings an underscore. It's mentioned somewhere in the docs, but you may as well ignore it. "int4" is the actual type name, "integer" is the sql standard name. PostgreSQL displays SQL compliant output where possible. _int simply doesn't exist, and oddities like (3) after the char does have array support at all... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] old Pg interface
On Mon, Oct 16, 2006 at 12:15:36PM -0500, Brandon Metcalf wrote: > I'm currently using version 1.9.0 of the old Pg interface with > PostgreSQL 8.0.3. Our code needs to be updated to use DBI/DBD::Pg, > but we need to upgrade PostgreSQL before this is going to happen. > Does anyone know of any issues with continuing to use the old Pg > interface with newer versions of PostgreSQL? I have a lot of code that still uses the old Pg interface. It works fine against newer versions. There's no requirement to change. If it ain't broke, don't fix it. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Dates rejected
On Mon, Oct 16, 2006 at 12:22:04PM -0200, Carlos H. Reimer wrote: > Hi, > > We´ve a simple insert that is not working. The strange thing is that all > kind of date are working with the exception of 15/10 (DD/MM) dates. > > create table tt_teste (datfis timestamp without time zone not null > CHECK (datfis = trunc(datfis::timestamp without time zone))); What are you trying to do here? If you only want a date, why not just use a date type? Have you tried evaluating the expression yourself? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
