Re: [GENERAL] Better way to process boolean query result in shell-like situations?
On Thu, Oct 29, 2015 at 02:42:00AM +, Tim Landscheidt wrote: > Hi, > > I regularly run into the problem that I want to query a > PostgreSQL database in a script/program and depending on a > boolean result do one thing or the other. A typical example > would be a Puppet Exec that creates a user only if it does > not exist yet. > > But unfortunately psql always returns with the exit code 0 > if the query was run without errors. In a shell script I > can use a query that returns an empty string for failure and > something else for success and then test that à la: What I do is use grep, for example (off the top of my head): if ! psql -qAt -c "select usename from pg_user" | grep -q USERNAME ; then ... If you're looking for true/false you could grep for t/f. Hope this helps, -- Martijn van Oosterhout <klep...@svana.org> http://svana.org/kleptog/ > The combine: one man, one day, wheat for half a million loaves of bread. signature.asc Description: Digital signature
Re: [GENERAL] Unit tests and foreign key constraints
On Thu, May 21, 2015 at 01:33:46PM -0700, Dave Owens wrote: I know some say your unit tests shouldn't touch the DB but the more full stack tests I have, the better I sleep at night :-)) Unit tests really should be about testing individual bits of logic. Does a single method do the desired thing, and not the undesired thing... Ideally, your data access layer should be interchangeable, ie: use a real database record in production, but use a mock database record for unit tests. Nice in theory. But if you use Postgres features like timestamptz calculations and hstore, it's generally way easier to run your unit tests on an actual PostgreSQL database. Otherwise you're going to spend all your time working around the fact that your mock database is not the real thing (and running into bugs in your emulation layer). Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Unit tests and foreign key constraints
On Thu, May 21, 2015 at 12:39:01PM -0700, Andy Chambers wrote: Hey All, I've started trying to use foreign key constraints in my schema but it seems to make it more difficult to write unit tests that touch the database because each test now requires more setup data to satisfy the foreign key constraint. (I know some say your unit tests shouldn't touch the DB but the more full stack tests I have, the better I sleep at night :-)) I wondered if anyone else has run into this problem and found a good strategy to mitigate it. I thought I might be able to make these constraints deferred during a test run since I have automatic rollback after each test but even after set constraints all deferred, I still got a foreign key violation during my test run if the test tries to insert data with a non-existent foreign key. Foreign keys aren't deferrable by default, you have to create them that way... Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] [HACKERS] optimization join on random value
On Mon, May 04, 2015 at 12:15:54AM +0300, Anton wrote: Hello guru of postgres, it's possoble to tune query with join on random string ? i know that it is not real life example, but i need it for tests. soe=# explain soe-# SELECT ADDRESS_ID, soe-# CUSTOMER_ID, soe-# DATE_CREATED, soe-# HOUSE_NO_OR_NAME, soe-# STREET_NAME, soe-# TOWN, soe-# COUNTY, soe-# COUNTRY, soe-# POST_CODE, soe-# ZIP_CODE soe-# FROM ADDRESSES soe-# WHERE customer_id = trunc( random()*45000) ; QUERY PLAN --- Seq Scan on addresses (cost=0.00..165714.00 rows=22500 width=84) Filter: ((customer_id)::double precision = trunc((random() * 45000::double precision))) (2 rows) If you look carefully you'll see that the comparison here is done as a double precision and so can't use the index. If you say something like: WHERE customer_id = trunc( random()*45000)::bigint it will probably work fine. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Prepared statements with bind parameters for DDL
On Wed, Feb 11, 2015 at 02:22:10PM -0500, Tom Lane wrote:
deepak deepak...@gmail.com writes:
I find that one can't have a prepared statement with bind parameters for a
DDL statement,
Nope. DDL commands generally don't have any support for evaluating
expressions, which would be the context in which parameters would
be useful. Nor have they got plans, which would be the requirement
for prepared statements to be good for much either.
Not really true, there are plenty of cases where you just want to fill
in literals without having to worry about quoting. For example:
DROP TABLE %s
is opening yourself up to SQL injection. I've wondered if it were
possible to be able to say:
DROP TABLE IDENTIFIER($1);
where in the grammer IDENTIFIER($x) would be parsed as an identifier
token and the parameter would be required to be a string. You don't
need to evaluate any expressions to make this work, but it saves you
from any quoting issues.
Of course, it gets more complicated if you want to allow cases like:
PREPARE get_by_id AS SELECT * FROM IDENTIFIER($1) WHERE id=$2;
EXECUTE get_by_id('mytable', 400);
But DDL would be a great start.
Have a nice day,
--
Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/
He who writes carelessly confesses thereby at the very outset that he does
not attach much importance to his own thoughts.
-- Arthur Schopenhauer
signature.asc
Description: Digital signature
Re: [GENERAL] sslcompression / PGSSLCOMPRESSION not behaving as documented?
On Fri, Jan 16, 2015 at 08:41:54AM -0800, Adrian Klaver wrote: Yes that would seem to be the issue: https://launchpad.net/ubuntu/trusty/+source/openssl/+changelog openssl (1.0.1e-3ubuntu1) Disable compression to avoid CRIME systemwide (CVE-2012-4929). FWIW, it's likely that the next version of TLS (version 1.3, see[1]) will no longer support compression at all. The concensus appears to be that this is the wrong level to be applying compression. Since the only way to get compression currently in Postgres is via TLS, perhaps we should look at supporting compression natively in future protocol versions. It will take a while for TLS 1.3 to be deployed so there's time, but PostgreSQL protocol revisions go at a similar pace. Have a nice day, [1] https://github.com/tlswg/tls13-spec -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Surrogate pairs in UTF-8
On Fri, Jan 16, 2015 at 08:16:47AM -0600, Dave Rosckes wrote: I have written a test program using postgres that creates a string with a surrogate pair. I then insert that string into a varchar property in a table. I then execute a select statement to pull the string out. But when I evaluate the string the lead char of the pair is correct, but the following pair value is mangled. I run this exact same code using DB2 and it works just fine. Is this a postgres limitation, or is there a specific way surrogate pairs need to be handled? Sounds odd. Can you provide actual queries showing the problem (and server version). Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Alternatives to a unique indexes with NULL
On Sat, Jan 17, 2015 at 02:03:34PM +0100, Andreas Kretschmer wrote: Peter Hicks peter.hi...@poggs.co.uk wrote: All, I have a Rails application on 9.3 in which I want to enforce a unique index on a set of fields, one of which includes a NULL-able column. According to http://www.postgresql.org/docs/9.3/static/indexes-unique.html, btree indexes can't handle uniqueness on NULL columns, so I'm looking for another way to achieve what I need. somethink like that? : test=# create table peter_hicks (id int); CREATE TABLE Time: 1,129 ms test=*# create unique index idx_1 on peter_hicks ((case when id is null then 'NULL' else '' end)) where id is null; CREATE INDEX Time: 14,803 ms Note: COALESCE is probably the better choice here. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Advice for using integer arrays?
On Tue, Jan 06, 2015 at 12:09:56PM -0500, Michael Heaney wrote: I'm fairly new to Postgres, and have a design issue for which an array of integers might be a good solution. But I'd like to hear from the experts before proceeding down this path. The biggest consideration is if you are ever intending to use the values in a join condition. Arrays a efficient space-wise and you also have good indexing strategies with GIN indexes. You will need to reframe your queries in terms of ([x] subset-of field) but that's relatively straightforward. What doesn't work or is fiddely: - foreign keys - selecting part of the list - reordering or otherwise manipulating the list. basically, if conceptually the list is a single object which you're really only going to want to access as a whole, but still want good indexing, then arrays are for you. BTW, looking at your example, you might be more interested in ranges, see for example: http://www.postgresql.org/docs/9.2/static/rangetypes.html Conceptually they are a bit different and there isn't support for multi-ranges AFAIK but they might be more appropriate. Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Recursive CTE trees + Sorting by votes
On Wed, Aug 06, 2014 at 05:28:09PM -0400, Gregory Taylor wrote: We are working on a threaded comment system, and found this post by Disqus to be super helpful: http://cramer.io/2010/05/30/scaling-threaded-comments-on-django-at-disqus/ The CTE works wonderfully, and we're really happy with the results. The last obstacle is figuring out how to sort by a votes field, meanwhile preserving the tree structure. What do you mean exactly? Do you mean that want everything at the same level to be sorted by vote? If we ORDER BY path, votes (assuming we have the same structure as in the article), we never need tie-breaking on path, so the votes part of this doesn't even come into the equation. I suspect we need to do some path manipulation, but I'm not too sure of where to begin with this. I attempted incorporating votes into the path, but I failed pretty badly with this. It's probably way off, but here's my last (failed) attempt: https://gist.github.com/gtaylor/e3926a90fe108d52a4c8 I think what you need to do is do the ordering withing the CTE itself. Something like: WITH RECUSIVE cte () AS ( SELECT ... ORDER BY vote DESC UNION ALL SELECT ... JOIN cte ... ORDER BY vote DESC ) SELECT * from cte; Or another idea, add a column that is the path of the parent: WITH RECUSIVE cte () AS ( SELECT array[] as path_parent, array[id] as path, ... ORDER BY vote DESC UNION ALL SELECT cte.path as path_parent, cte.path || comments.id as path, ... JOIN cte ... ORDER BY vote DESC ) SELECT * from cte order by path, votes desc; Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Random-looking primary keys in the range 100000..999999
On Fri, Jul 04, 2014 at 09:24:31AM -0400, Kynn Jones wrote: I'm looking for a way to implement pseudorandom primary keys in the range 10..99. The randomization scheme does not need to be cryptographically strong. As long as it is not easy to figure out in a few minutes it's good enough. Well, a trick that produces a not too easy to guess sequence is: X(n) = p^n mod q where q is prime. Pick the largest prime that will fit, in this case 899981 (I beleive) and some random p, say 2345. Then 10 + (2345^n) mod 899981 should be a sequence fitting your purpose. Unfortunatly, the pow() function in Postgres can't be used here (too slow and it overflows), but python has a helpful function: In [113]: len( set( pow(2345, n, 899981) for n in range(899981) ) ) Out[113]: 899980 You could probably write an equivalent function in Postgres if necessary. Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] pg_attribute growing extremely
On Fri, Jun 06, 2014 at 03:56:23AM -, haman...@t-online.de wrote: Hi, on a server running 8.4 I observe that, since a while, the pg_attribute table is growing enormously. Soon after reloading I have one file ls -s 1249 1048580 1249 a day later this is 1048580 1249 1048580 1249.1 1048580 1249.10 1048580 1249.11 1048580 1249.12 1048580 1249.13 1048580 1249.14 1048580 1249.15 682212 1249.16 1048580 1249.2 1048580 1249.3 1048580 1249.4 1048580 1249.5 1048580 1249.6 1048580 1249.7 1048580 1249.8 1048580 1249.9 4316 1249_fsm 24 1249_vm and 5 days later the system had arrived at 102 files Is autovacuum enabled? Are you using a lot of temporary tables? Do you have long running transactions? Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: Fwd: [GENERAL] libpq: indefinite block on poll during network problems
On Fri, May 30, 2014 at 07:48:00PM +0400, Dmitry Samonenko wrote: BTW, you might consider using libpq's nonblock mode to push the waiting out to the application level, and then you could just decide when you've waited too long for yourself. Do you mean PQsendQuery / PQisBusy / PQgetResult? Well, I wouldn't start this discussion if that was an option. Adopting async command processing would lead to starting client from scratch. I don't think the suggestion is to move to async command processing. I think the suggestion is to use those methods to make a PGgetResultWithTimeout() that does what you want. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: Fwd: [GENERAL] libpq: indefinite block on poll during network problems
On Thu, May 29, 2014 at 12:27:50PM +0400, Dmitry Samonenko wrote: Guys, first of all: thank you for you help and cooperation. I have received several mails suggesting tweaks for tcp_keepalive and usage of postgresql server functions/features (cancel, statement timeout), but as I said - it won't help. I have reproduced the problem scenario. Logs are attached. I walk you through. == Setup == Client and server applications are placed on separate hosts. Client = 192.168.15.4, Server = 192.168.15.7. Both are in local net. Both are synchronized using 3rd party NTP server. Lets look in strace_export.txt - top 8 lines = socket setup. Keepalive option is set. Client's OS keepalive parameters: [root@krr2srv1wsn1 dtp_generator]# sysctl -a | grep keepalive net.ipv4.tcp_keepalive_intvl = 5 net.ipv4.tcp_keepalive_probes = 3 net.ipv4.tcp_keepalive_time = 10 This means that after 10 seconds of idle connection first TCP Keep-Alive probe is sent. If 3 probes with 5 second interval fail - connection should be considered dead. Something very important to note: those settings do nothing unless the SO_KEEPALIVE option is turned on for the socket. AFAICT libpq does not enable this option, hence they (probably) have no effect. (Discovered after finding processes staying alive for several months because the firewall had lost it's state table at some point). Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] new index type with clustering in mind.
On Sat, May 24, 2014 at 05:58:37PM +0100, Jack Douglas wrote: Would the following be practical to implement: A btree-like index type that points to *pages* rather than individual rows. Ie if there are many rows in a page with the same data (in the indexed columns), only one index entry will exist. In its normal use case, this index would be much smaller than a regular index on the same columns which would contain one entry for each individual row. To reduce complexity (eg MVCC/snapshot related issues), index entries would be added when a row is inserted, but they would not be removed when the row is updated/deleted (or when an insert is rolled back): this would cause index bloat over time in volatile tables but this would be acceptable for the use case I have in mind. So in essence, an entry in the index would indicate that there *may* be matching rows in the page, not that there actually are. It's an interesting idea, but, how can you *ever* delete index entries? I.e. is there a way to maintain the index without rebuilding it regularly? Maybe there's something you could do with tracking all the entries that point to one page or something, or a counter. Because really, the fact that the item pointer in a btree index includes the item number is only really needed for deletion. Postgres always has to read in the whole page anyway, so if you can find a way around that it might be an interesting improvement. Mind you, hash indexes could get this almost free, except they're not crash safe. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Refresh Postgres SSL certs?
On Wed, Apr 09, 2014 at 12:28:14PM -0700, Paul Jungwirth wrote:
Hello,
In light of the Heartbleed OpenSSL bug[0,1], I'm wondering if I need
to regenerate the SSL certs on my postgres installations[2] (at least
the ones listening on more than localhost)? On Ubuntu it looks like
there are symlinks at /var/lib/postgresql/9.1/main/server.{crt,key}
pointing to /etc/ssl/private/ssl-cert-snakeoil.{pem,key}. Is there any
documentation on how to regenerate these? Are they self-signed? Can I
replace them with my own self-signed certs, like I'd do with Apache or
Nginx?
Have you read the Debian README?
/usr/share/doc/postgresql-*/README.Debian.gz
It talks about how the certificates are made. It uses the ssl-cert
package to make them, there's more docs there.
Yes, you can make your own self-signed certs and use them.
Have a nice day,
--
Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/
He who writes carelessly confesses thereby at the very outset that he does
not attach much importance to his own thoughts.
-- Arthur Schopenhauer
signature.asc
Description: Digital signature
Re: [GENERAL] Refresh Postgres SSL certs?
On Wed, Apr 09, 2014 at 12:59:53PM -0700, Paul Jungwirth wrote: Have you read the Debian README? /usr/share/doc/postgresql-*/README.Debian.gz Thank you for pointing me to that file. From /etc/share/doc/ssl-cert/README it sounds like the old snakeoil cert is already self-signed, so that's promising. So I take it that psql and the postgres client library won't object to a self-signed cert. Do they do any kind of certificate pinning or other caching of the old cert? Or can I just replace the cert, restart the postgres server, and be done? No pinning, no caching. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] encrypting data stored in PostgreSQL
On Wed, Apr 09, 2014 at 02:16:34PM -0700, Ken Tanzer wrote: Any thoughts on how to pull this off for PostgreSQL stored data? I looked at this a while ago because I have clients who might require this in the future. ISTM you should be able to have your PG data directory stored on an encrypted filesystem. I believe this will decrease performance, but I have no idea by how much. FWIW, I have several databases running on encrypted filesystems. The performance difference is negligable *if* you have hardware acceleration for your encryption, which most modern processors have. Essentially, the processor can encrypt/decrypt data so much faster than the cost of reading/writing to disk, you don't notice the difference. There's surely a difference, but if this means you meet your requirements it's an excellent solution. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] 9.3 debian install setup failure
On Fri, Mar 21, 2014 at 05:47:54PM -0600, john.tiger wrote: 9.3 install on debian jessie 64amd Firstly, you did read: /usr/share/doc/postgresql-9.3/README.Debian.gz right? sudo -u postgres psql = worked alter user postgres with password 'password' = alter role Why on earth would you want to do that. The default is peer, so you don't need a password to login as the postgres user. su - postgress enter password = authentication failure hba.conf edited to allow trust to local - shouldn't this be set up as standard default ? Did you not read the comment above it saying you should change that if you expect the default maintainence scripts to keep working? still authentication failure (after rebooting) now sudo -u postgres psql = could not connect - is server running - shouldn't install set up server to be booted on start up ? Check if the server is actually running with ps. Maybe you typoed the config file? Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
[GENERAL] WAL archive cleanup om master
Hoi, I've setup a up WAL shipping configuration as described in the wiki. On the master I have: archive_mode= on archive_command = 'cp %p /path_to/archive/%f' and on the slave in the recovery.conf I have: archive_cleanup_command = 'pg_archivecleanup /path/to/archive/ %r' restore_command = 'cp /path/to/archive/%f %p' In between the two machines I have an rsync process copying the files from the master to the slave. My problem is, that the archive_cleanup_command on the slave is clearing old WAL files, which the rsync on the master just copies again because they are still on the master. I can make a script on the master that deletes files older than an hour, but that will break horribly if the copying breaks for an hour. Is there a smarter way to do this, like having rsync not copy stuff already copied once? Thanks in advance, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] WAL archive cleanup om master
On Mon, Jan 06, 2014 at 07:16:25AM -0800, Adrian Klaver wrote: On 01/06/2014 03:18 AM, Martijn van Oosterhout wrote: Hoi, I've setup a up WAL shipping configuration as described in the wiki. On the master I have: archive_mode= on archive_command = 'cp %p /path_to/archive/%f' and on the slave in the recovery.conf I have: archive_cleanup_command = 'pg_archivecleanup /path/to/archive/ %r' restore_command = 'cp /path/to/archive/%f %p' In between the two machines I have an rsync process copying the files from the master to the slave. My problem is, that the archive_cleanup_command on the slave is clearing old WAL files, which the rsync on the master just copies again because they are still on the master. I can make a script on the master that deletes files older than an hour, but that will break horribly if the copying breaks for an hour. Is there a smarter way to do this, like having rsync not copy stuff already copied once? So the rsync and archive_command combination is a belt and suspenders thing? I'm not sure what you mean, isn't this the recommended way of doing things? The configuration comes from here: http://wiki.postgresql.org/wiki/Hot_Standby The master saves the archives to a directory, rsync copies them to the slave, where there restore_command can find them. I suppose you could get the archive_command to rsync directly, but will that work in the case of network failure? Note that from a network topology view, the slave cannot connect to the master, so streaming replication is out. As to rsync, if I understand what you want you might take a look at --existing: http://linux.die.net/man/1/rsync I don't think that will help me, since the rsync is supposed to copy the new WAL files. The problem is that it needs to *not* copy the old ones. However, right under that switch I see --remove-source-files which looks closer, as long as I only have one slave that is. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] WAL archive cleanup om master
On Mon, Jan 06, 2014 at 08:17:37AM -0800, Adrian Klaver wrote: On 01/06/2014 07:35 AM, Martijn van Oosterhout wrote: On Mon, Jan 06, 2014 at 07:16:25AM -0800, Adrian Klaver wrote: On 01/06/2014 03:18 AM, Martijn van Oosterhout wrote: Hoi, I'm not sure what you mean, isn't this the recommended way of doing things? The configuration comes from here: http://wiki.postgresql.org/wiki/Hot_Standby The master saves the archives to a directory, rsync copies them to the slave, where there restore_command can find them. Well this is the above is where is could be redundant. In your original post it would seem the archive_command and restore_command are pointing at the same directory. I realize they are just placeholder names, so is that the case? If so I am not sure what the rsync accomplishes. If not why not just make it so? Well, they're the same directory on different machines. The directory is actually /var/lib/postgresql/9.2/xlogs on both, but that's not really relevent. There's a cronjob on the master that says: rsync -avz /var/lib/postgresql/9.2/xlogs/* slave:/var/lib/postgresql/9.2/xlogs/ The question is: what is it that prevents the WAL files in /var/lib/postgresql/9.2/xlogs from filling the disk on the master? The minimal cover your bases setup is usually given as: Primary(Machine1) -- Archive -- Archive Directory(Machine2) | | | StreamingRestore | - Standby(Machine3) - Excuse the ASCII art. The ascii art is fine, but you have the same problem: on the Machine1 the WAL files are stored prior to copying to Machine2. How do you know when you can delete files safely on Machine1? Anyway, I found this page on the wiki: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial which says that: archive_command = 'rsync -a %p 192.168.0.2:/var/lib/pgsql/data/archive/%f' is acceptable and also avoids the problem. I'll just test what happens with network failure (hopefully it doesn't kill the master). Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] question on IPC vs TCPIP
On Wed, Jan 01, 2014 at 03:55:50PM -0500, Andrew McIntyre wrote: Does postgres have an equivalent C level (or ODBC) parameter so you can use IPC for local to db server only code? http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0008807.html There is a measurable reduction in elapsed time for my code when I specify IPC. My code uses: node.js https://npmjs.org/package/odbc db2 In general, in postgres you leave the the hostname blank to specify local IPC. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] having difficulty with explain analyze output
On Tue, Nov 26, 2013 at 02:43:42PM -0500, David Rysdam wrote: I'm not really looking for information on how to speed this query up. I'm just trying to interpret the output enough to tell me which step is slow: Seq Scan on mags (cost=0.00..187700750.56 rows=47476 width=4) (actual time=3004851.889..3004851.889 rows=0 loops=1) Filter: ((signum IS NOT NULL) AND (NOT (SubPlan 1))) SubPlan 1 - Materialize (cost=0.00..3713.93 rows=95862 width=4) (actual time=0.011..16.145 rows=48139 loops=94951) - Seq Scan on sigs (cost=0.00..2906.62 rows=95862 width=4) (actual time=0.010..674.201 rows=95862 loops=1) Total runtime: 3004852.005 ms It looks like the inner seq scan takes 674ms, then the materialize takes an additional 16ms? Or is that 16ms * 94951? Or 674 * 94951? The Seq Scan took 674ms and was run once (loops=1) The Materialise was run 94951 times and took, on average, 0.011ms to return the first row and 16ms to complete. 16.145 * 94951 = 1532983.895 And the outer seq scan takes 3004851-3004851 = 0ms? The outer plan took 3004851ms to return its first row, and last row also as apparently it matched now rows at all. And if this is the complete plan, it took 1,500 seconds for itself. Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
[GENERAL] Changing primary key of large table, with foreign keys, without locking
I have a database with a rather large events table defined something like: # create table events(a int4, b int4, primary key(b,a)); CREATE TABLE There are more columns, but thy are not relevent here. What I do have is 7(!) tables that reference this one like so: # create table attr1(a int4, b int4, foreign key (a,b) references events(a,b)); Note the foreign key references (a,b), the index on (b,a) is not really useful since lookups by b only are pointless. There is also an index on events(a) which I'd like to remove by simply making a primary key on (a,b). This table has 100 million rows and downtime is somewhat undesirable. What I thought I'd do is create a new primary key index, create new foreign keys, fake the validation and then drop the old primary key. But it requires some catalog manipulation which I'm pretty sure is safe, but I hope others can verify. We start with: # \d events Table public.events Column | Type | Modifiers +-+--- a | integer | not null b | integer | not null Indexes: events_pkey PRIMARY KEY, btree (b, a) Referenced by: TABLE attr1 CONSTRAINT attr1_a_fkey FOREIGN KEY (a, b) REFERENCES events(a, b) So I've come up with the following schema: Step 1: Create a new primary key, without locking # create unique index concurrently events_a_b_pkey on events(a,b); # update pg_index set indisprimary=false where indrelid='events'::regclass and indisprimary=true; # alter table events add primary key using index events_a_b_pkey; This should be safe because marking an index non-primary doesn't change anything really. Now we have: # \d events Table public.events Column | Type | Modifiers +-+--- a | integer | not null b | integer | not null Indexes: events_a_b_pkey PRIMARY KEY, btree (a, b) events_pkey UNIQUE, btree (b, a) Referenced by: TABLE attr1 CONSTRAINT attr1_a_fkey FOREIGN KEY (a, b) REFERENCES events(a, b) Step 2: create new foreign keys, wthout locking # alter table attr1 add foreign key (a,b) references events not valid; # update pg_constraint set convalidated=true where conname='attr1_a_fkey1'; This is safe because it's identical to the other foreign key, except that the dependancies are different. Note it is very important *not* to specify the columns on the events table, or PostgreSQL picks the wrong index to associate with. Now we have: # \d events Table public.events Column | Type | Modifiers +-+--- a | integer | not null b | integer | not null Indexes: events_a_b_pkey PRIMARY KEY, btree (a, b) events_pkey UNIQUE, btree (b, a) Referenced by: TABLE attr1 CONSTRAINT attr1_a_fkey FOREIGN KEY (a, b) REFERENCES events(a, b) TABLE attr1 CONSTRAINT attr1_a_fkey1 FOREIGN KEY (a, b) REFERENCES events(a, b) Step 3: Remove original primary key # alter table events drop constraint events_pkey cascade; NOTICE: drop cascades to constraint attr1_a_fkey on table attr1 ALTER TABLE And we're done! # \d events Table public.events Column | Type | Modifiers +-+--- a | integer | not null b | integer | not null Indexes: events_a_b_pkey PRIMARY KEY, btree (a, b) Referenced by: TABLE attr1 CONSTRAINT attr1_a_fkey1 FOREIGN KEY (a, b) REFERENCES events(a, b) Voila! Am I missing anything? It's not pretty, but it reduces the problem to a few short exclusive locks, rather than hours of downtime scanning tables. PG 9.1.10 FWIW. -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Postgres as In-Memory Database?
On Sun, Nov 17, 2013 at 10:33:30PM +0100, Stefan Keller wrote: I think I have to add, that pure speed of a read-mostly database is the main scenario I have in mind. Duration, High-availability and Scaling out are perhaps additional or separate scenarios. So, to come back to my question: I think that Postgres could be even faster by magnitudes, if the assumption of writing to slow secondary storage (like disks) is removed (or replaced). If your dataset fits in memory then the problem is trivial: any decent programming language provides you with all the necessary tools to deal with data purely in memory. There are also quite a lot of databases that cover this area. PostgreSQL excels in the area where your data is much larger than your memory. This is a much more difficult problem and I think one worth focussing on. Pure in memory databases are just not as interesting. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Curious question about physical files to store database
On Tue, Nov 05, 2013 at 10:42:36PM +0800, Patrick Dung wrote: I have seen some databases product that allocate small number of large files. Please correct me if I am wrong: MySQL with InnoDB Actually, InnoDB has a file-per-table mode which I tend to prefer. It means that when I drop a partition I actually see the disk usage drop. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Suitable Index for my Table
On Mon, Nov 04, 2013 at 07:21:11PM +0100, Janek Sendrowski wrote: Hi, I've got a table with many Values of the Type REAL. These are my metric distances or my pivots to my sentences. The table looks like this: ID INTEGER, distance1 REAL, distance2 REAL, distance3 REAL, distance4 REAL, ..., distance24 REAL The range of the Value is in between 0 and 1. So it looks like this 0.196 or 0.891 That my query WHERE value BETWEEN (distance1 - radius) AND (distance1 + radius) WHERE value BETWEEN (distance2 - radius) AND (distance2 + radius) WHERE value BETWEEN (distance3 - radius) AND (distance3 + radius) WHERE value BETWEEN (distance4 - radius) AND (distance4 + radius) ... Now I'm searching for a suitable index. This sounds like a job for a geometric datatype, a la GiST. http://www.postgresql.org/docs/9.3/static/cube.html CREATE INDEX foo ON bar USING GIST ( cube( ARRAY(distance1), ARRAY(distance1) ) ); The you can do lookups with: SELECT * FROM bar WHERE cube( ARRAY(distance1), ARRAY(distance1) ) cube( ARRAY(value-radius), ARRAY(value+radius) ) If you commonly use sets of columns you can go multiple dimensional for extra benefit. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Suitable Index for my Table
On Mon, Nov 04, 2013 at 10:44:29PM +0100, Janek Sendrowski wrote: I am always searching for single colums. The values are different every time, because these are distances from my new sentence to my pivots. Does the Index support a query with this WHERE statement: WHERE value BETWEEN (distance1 - radius) AND (distance1 + radius)? Ok, this is not consistant. You say you're only searching single columns, but in your example query you're doing 23 columns in one query. A working query looks like this: SELECT id FROM distance WHERE value BETWEEN (distance1 - radius) AND (distance1 + radius) AND value BETWEEN (distance2 - radius) AND (distance2 + radius) AND value BETWEEN (distance3 - radius) AND (distance3 + radius) AND So you're looking for the same value amongst all distances? That's doesn't seem very useful. Then if distance1=0 and distance2=1 and radius=0.2 then this query will never match that row, no matter what value of value. Anyway, unless you can describe this problem as something geometric (such that you can consider it a search for overlapping cubes) I'd do what the other post suggests and just put a btree index on every column. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] [GENERAL] Urgent Help Required
On Tue, Oct 08, 2013 at 08:06:50AM -0700, bricklen wrote: On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh shaileshj...@gmail.comwrote: HINT: To avoid a database shutdown, execute a full-database VACUUM in patnadbold. ERROR: could not access status of transaction 33011 DETAIL: could not open file pg_clog/: No such file or directory exit After this i am able to stop /start my db server but i am not able to connect to my databases (it tells to run vacuum full first on patnadbold databases) The message does *not* say to run VACUUM FULL, it says to run a full-database VACUUM. Different things. Connect to patnadbold and issue VACUUM; (without double-quotes) as the others have suggested. In case it isn't clear to the original poster, VACUUM FULL will take a lot longer than a simple VACUUM and probably not really help much. Just plain VACUUM. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Building with MinGW issue
On Sun, Sep 29, 2013 at 07:09:40AM +0200, Muhammad Bashir Al-Noimi wrote: On 09/27/2013 10:57 PM, Adrian Klaver wrote: Did you try the override method shown in this message?: http://www.postgresql.org/message-id/ovpbg9.x5ovpbg9.pq9n.w333.g...@asuka.myrkraverk.com I found it very comlicated and made things worst (I got ton of errors)!!! BTW, I wonder why Postgresql guys doesn't care about open source compilers like other projects; they're part of open source community why they support commercial compilers and drop the open source!!! Umm, this *is* on a proprietry platform. The problem is that if it isn't tested much then people don't find the problems... We try to support as many platforms as possible, but are limited by what people actually use. And in this case it's not the compiler that's the problem but the system libraries, which are different from every other platform. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] procedure to contribute this community
On Mon, Apr 08, 2013 at 07:45:16AM +1000, Chris Angelico wrote: It seems that good software works really well with other good software. Pike and PostgreSQL and Linux work beautifully together; VB .NET and PostgreSQL and Windows, not so much. I wonder if that's because smart developers use awesome tools, and so build the linkages between them first, and only support the less-awesome tools later on as someone else asks for it... The best explanation I've heard is that open source solves the problem of bad interfaces. By this I mean that if you're having a performence problem or struggling with an API, you can simply download the source of the component and look for where the problem is. Then you can either tweak your program with perfect understanding that it will work *or* fix the library so the problem doesn't happen. Both lead to more robust software. It happens regularly that some performence problem on a particular OS (other than windows) ends with someone digging up the code in the kernel source that's causing the problem. With Windows you're coding to a black box, which means you end up with hacks and workarounds at all levels of the stack leading to associated performence problems and instability. Someone wrote a nice blog about it once and coined a term, but I've forgetten what. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Why does slony use a cursor? Anyone know?
On Tue, Mar 05, 2013 at 08:51:11AM -0600, Shaun Thomas wrote: Hey everyone, Frankly, I'm shocked at what I just found. We did a delete last night of a few million rows, and come back this morning to find that slony is 9-hours behind. After some investigation, it became apparent that slony opens up a cursor and orders it by the log_actionseq column. Then it fetches 500 rows, and closes the cursor. So it's fetching several million rows into a cursor, to fetch 500, and then throw the rest away. I once had a similar problem and it looked like they were missing an index. I asked about it and it turns out that I was running a somewhat old version, and it was fixed in later versions. Check that first. But ask anyway, I've always found the Slony guys very helpful. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] New Zealand Postgis DBA job vacancy
On Wed, Dec 26, 2012 at 02:45:18PM -0500, Berend Tober wrote: I guess I was hoping for more practical, direct insight, such as Despite how skilled and a productive worker you might be, don't bother applying if you are anywhere near middle-age, or if you are likely to become middle-aged. There was this interesting 1976 movie called Logan's Run about a dystopian future that similarly devalued the experienced. If it's like the Australian system then there several different categories you can come in on. A way it look at it is that you can come if you can demonstrate that you will not require welfare support, either by bringing enough money with you or having skill in some area where there is demand. But then there's an exception for people under 30 who are unlikely to have been able to build anything up yet, you assume that if they have any kind of degree then they will pay for themselves in the long run. I wonder, if an applicant were within the government-approved age range, but then worked long enough so as to exceed the limits, would their work visa suddenly be withdrawn, having contributed to the tax base and maybe established a family there, then be forced to vacate the premise simply because they aged out? In my experience, as long as you're working there's never a problem (assuming your visa allows you to work). Marrying an Australian has to be one of the more common ways to get permanent residency, since that is one of the easier ways. I've never heard of anything like the weird cases you get with those American green cards. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] integer instead of 'double precision'?
On Mon, Nov 12, 2012 at 02:16:21PM +0100, Willy-Bas Loos wrote: On Fri, Sep 9, 2011 at 5:09 PM, Guillaume Lelarge guilla...@lelarge.infowrote: You divide an integer with an integer, that should give you an integer. Can you tell me the reasoning behind that idea? Is it a rule that the output type of an operator must equal the input type? In this case that doesn't seem locigal. I think that the / operator should return something that allows fractions, since the operator creates fractions so frequently. The thing is, you often do need the version that truncates. It's supported by the underlying system and if you want a float as output you can cast one of the arguments to float to do that. It's been like this forever (C does it too for example). For integers it may help if you think of it in combination with the modulus operator (%). Python 3 recently changed to give float output by default, but also provides a // operator to access the truncated version. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] PostgreSQL and IPV6
On Sat, Nov 03, 2012 at 09:11:51AM +0100, Yvon Thoraval wrote: I'm using to computers : - a laptop under Xubuntu 12.04 with PostgreSQL 9.1 - a desktop under Mac OS X Mountain Lion with PostgreSQL 9.2 After the switch to Mountain Lion, i had a small prob connecting to a database on my laptop. snip However when connecting from desktop to laptop, altough the IPV6 address of my desktop is in my pg_hba.conf,psql rejected the connection because an address terminating by 18cf isn't in my pg_hba.conf. Then i did verify my mac os x setup showing that this address is a valid one for my desktop, in fact my desktop does have up to eight IPV6 addresses. IIRC MacOS X uses the IPv6 privacy extensions which means that clients will regularly get different source IPs. The machine does this by adding a new address every now and then. A side effect of this is that you can't firewall on specific IP. A few things I can think of: - Find a way to fix the IP address. - Use the link-local address (beginning with fe80) as they won't change. Only works on a single network ofcourse. - Allow the whole subnet, rather than individual IPs. Why, in one direction from laptop to desktop i use successfully : psql -h IPV6-terminating-by-2559 and the other way, from desktop to laptop this is IPV6-terminating-by-18cf being seen by the laptop's PostgreSQL ? Linux does not use privacy extensions by default, so the IP address doesn't change. Maybe that explains it? Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Somewhat automated method of cleaning table of corrupt records for pg_dump
On Mon, Oct 22, 2012 at 11:54:47AM +0200, Heiko Wundram wrote: If there's any other possibility of out of the box recovery - except writing myself a small script to walk all rows - I'd still be grateful for a hint. Something that has worked for me in the past is: $ SELECT ctid FROM table WHERE length(field) 0; This gives you a list of ctids (if it works) which you can delete. You can also look for very large lengths. This works because length() doesn't actually unpack the string, it just pulls the length. It doesn't always work, it depends on the kind of corruption. You also need to start at the leftmost text field and work forwards, because it blows up while unpacking the tuples. Otherwise you're back to doing things like: $ SELECT sum(length(field || '')) FROM table OFFSET x LIMIT y; And doing a bisect type algorithm to narrow down where it is. The sum(length()) is so you throw away the output after checking field can be extracted properly. Once you get close you start printing the ctids and take a stab at the ctid of the broken row and delete it. If your table is large the OFFSET/LIMIT get slow. Unfortunatly Postgres doesn't understand clauses like 'ctid (page,tuple)' to start scanning at a particular spot in the table. It's not automated, though it might not be hard to do. Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Securing .pgpass File?
On Mon, Oct 01, 2012 at 11:02:39PM -, Greg Sabino Mullane wrote: Has anyone come up with a good solution for distributing a .pgpass file that doesn't expose it to anyone who has access to the distribution mechanism? No, you cannot easily keep it in version control/puppet securely. In my experience we've handled this in a few ways: - Postgres has ident auth, which avoids the use of password for local accounts, which helps in a lot of cases. - Puppet has the concept of an external node classifier. This is a script which gets called with the name of the node, and can return data which is then available to the puppet run. So the password is stored elsewhere (in our case Foreman) which means you only need to worry about the people who can access the Foreman server or the puppet server. Note people who can check arbitrary things into the repo and view the puppet output would be able to get the password by printing it to the debug log. - Punt. Check in the password but set the access controls so it only work for very few IPs, then you only need to worry about people who can log into *those* machines. Which is controlled by public SSH keys which you can check-in safely. Not super safe, but for read-only accounts for e.g. nagios might be ok. Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Thousands of schemas and ANALYZE goes out of memory
On Tue, Oct 02, 2012 at 10:38:38AM -0700, Hugo Nabble wrote: That might be the problem. I think with 32 bits, you only 2GB of address space available to any given process, and you just allowed shared_buffers to grab all of it. The address space for 32 bits is 4Gb. We just tried to reach a balance in the configuration and it seems to be working (except for the ANALYZE command when the number of schemas/tables is huge). Are you sure about that? You don't say what OS you are using but on Linux 3Gb is normal and on Windows 2Gb. Here are some nice diagrams: http://duartes.org/gustavo/blog/post/anatomy-of-a-program-in-memory In my experience it's better to keep the shared buffers around your working set size and let the kernel cache the rest as needed. Setting the shared_buffers to 1Gb will give your server much more breathing space for large operations like what you are asking. Note that unlike the way some other database servers work, the shared_buffers is the *minimum* postgres will use, not the maximum. Some questions I have: 1) Is there any reason to run the ANALYZE command in a single transaction? 2) Is there any difference running the ANALYZE in the whole database or running it per schema, table by table? I don't think it does do everything in a single transaction, though I can imagine that if you try to analyse the whole database it uses up more memory to track the work it has to do. With 220,000 tables I imagine this could add up. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] pg_typeof equivalent for numeric scale, numeric/timestamp precision?
On Tue, Oct 02, 2012 at 10:19:18AM +0800, Craig Ringer wrote: Hi all While examining a reported issue with the JDBC driver I'm finding myself wanting SQL-level functions to get the scale and precision of a numeric result from an operation like: select NUMERIC(8,4) '1.' union select INTEGER 4; The typmod in postgres is not maintained very well. In the wire-protocol the typmod is provided if known (the C interface calls the function PQfmod) and if it's not there (which is very often, postgres doesn't try very hard) there's no way to encourge postgres to work it out for you. As for no SQL level functions, you could probably write a function to determine the scale/precision of a given *value*, but not for a whole column. But once you have to string representation of the value you have that anyway... Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] 9.2 and index only scans
On Sun, Aug 26, 2012 at 11:01:31PM +0200, Thomas Kellerer wrote: I was inspired by this question on StackOverflow: http://stackoverflow.com/questions/12128501/fastest-way-to-count-the-rows-in-any-database-table/12128545#12128545 Which shows Oracle's behaviour with an index scan for the count(*) operation. Interesting, It shows indeed Oracle uses the index to do the operation. For postgres it's not so simple for a few reasons, I'm not sure how oracle avoids the same issues: - The index has no visibility information, so you can't tell if an index entry refers to a row you can actually see in your session. The visibility map might help here in the future. - Different versions of the same row (after an UPDATE for example) may both be in the index, Now if you're counting a primary key column you can work around that. But frankly, counting all the rows in a table is something I never do. The system tables carry estimates which have proved good enough for statistical purposes when I need them. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Rules, Windows and ORDER BY
On Fri, Aug 24, 2012 at 09:32:32AM +, Jason Dusek wrote: 2012/8/23 Tom Lane t...@sss.pgh.pa.us: Jason Dusek jason.du...@gmail.com writes: CREATE TABLE kv ( k bytea NOT NULL, at timestamptz NOT NULL, realm bytea NOT NULL, v bytea NOT NULL ); CREATE INDEX ON kv USING hash(k); CREATE INDEX ON kv (t); CREATE INDEX ON kv USING hash(realm); SELECT * FROM kv WHERE k = $1 AND realm = $2 ORDER BY at DESC LIMIT 1; If you want to make that fast, an index on (k,realm,at) would help. Those indexes that you did create are next to useless for this, and furthermore hash indexes are quite unsafe for production. Why are the individual indices not useful? The tests that the query does -- equality on key and realm and ordering on at -- are each supported by indices. Does it have to do with the cost of loading the three indices? I'm not entirely sure, but I'll take a stab at it. I think it has to do with the fact that you want order. Combining multiple indexes so you use them at the same time works as an BitmapAnd. That is, it uses each index to determine blocks that are interesting and then find the blocks that are listed by all tindexes, and then it loads the blocks and chcks them. The problem here is that you want ORDER BY at, which makes the above scheme fall apart, because order is not preversed. So it falls back on either scanning the 'at' index and probing checking the rows to see if they match, or using all indexes, and then sorting the result. In theory you could BitmapAnd the 'k' and 'realm' indexes and then scan the 'at' index only checking rows that the bitmap shows are interesting. But I'm not sure if postgres can do that. Anyway, the suggested three column index will match your query in a single lookup and hence be much faster than any of the above suggestions, so if this is a really important query then it may be worth it here. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Alternatives to very large tables with many performance-killing indicies?
On Wed, Aug 22, 2012 at 05:56:27PM -0700, Jeff Janes wrote: It's wide-ish, too, 98 columns. How many of the columns are NULL for any given row? Or perhaps better, what is the distribution of values for any given column? For a given column, is there some magic value (NULL, 0, 1, -1, , '') which most of the rows have? In particular, if the data is sparse, as in lots of NULLs, and you don't need to search on those, you might consider partial indexes. If you create partial indexes for only the non-NULL entries, postgres is smart enough to use it when you query it for something not NULL. Example: db=# create temp table foo (a int4, b int4); CREATE TABLE db=# insert into foo (a) select generate_series(1,10); INSERT 0 10 db=# update foo set b=1 where a=1; UPDATE 1 db=# create index bar on foo(b) where b is not null; CREATE INDEX db=# explain select * from foo where b=1; QUERY PLAN Bitmap Heap Scan on foo (cost=4.38..424.59 rows=500 width=8) Recheck Cond: (b = 1) - Bitmap Index Scan on bar (cost=0.00..4.26 rows=500 width=0) Index Cond: (b = 1) (4 rows) In this case a row update will only update indexes with non-NULL rows, which may cut the overhead considerably. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] How hard would a path operator be to implement in PostgreSQL
On Tue, Aug 21, 2012 at 09:39:20AM +0800, Craig Ringer wrote: On 08/21/2012 03:06 AM, Martijn van Oosterhout wrote: I'm not sure I have an opinion on pushing ORM features to the database layer, SQLAlchemy is doing a pretty good job for me already. There are some things ORMs could really use help from the database with, though. Particularly when fetching larger object graphs with deep relationships. The classic ORM chained-left-join pattern leads to *awful* explosions of join sizes, and huge amounts of duplicate data being sent. The n+1 selects alternative is even worse. Well, Postgres in principle supports arrays of records, so I've wondered if a relationship join could stuff all the objects in a single field of the response using an aggregate. I think what's always prevented this from working is that client would have to parse the resulting output text output, which is practically impossible in the face of custom types. What seems more useful to me is working on returning multiple resultsets, which could be interleaved by the server, so you could do things like (syntax invented on the spot, essentially WITHs without an actual query): WITH order_result AS (SELECT * FROM orders WHERE interesting) WITH widget_result AS (SELECT * FROM widgets WHERE widgets.order=order_result.id); Here the server could perform the join and return both sides of the join in seperate result sets. But named, so they can refer to eachother. I suppose for backward compatability you'd have a master result set with named children, otherwise the memory management gets hairy. And I have no idea if the BE/FE protocol can handle it, but it would be useful, and I think easy for ORMs to use, since they can stuff the user query in the first bit, and tack their relationship joins on the end. If the bare WITHs look like it might be ambiguous, you could make the actual query something like: ... RETURNS order_result, widget_result; which might be better since it allows the original query to use WITHs without interfering with the result. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] How hard would a path operator be to implement in PostgreSQL
On Sun, Aug 19, 2012 at 06:28:57PM -0700, Chris Travers wrote: In DB2 this might be done like: SELECT * FROM address WHERE address-country-short_name = 'US'; I like DB2's approach better because there is no ambiguity between namespace resolution but I don't entirely like the way the refs work as separate from standard foreign keys. I'm not sure I have an opinion on pushing ORM features to the database layer, SQLAlchemy is doing a pretty good job for me already. I just wanted to say that in the beginning I didn't understand SQLAchemy's separation between foreign keys and relationships, but I have since come to understand that a relationship is a far more general concept than a foreign key. There are many kinds of relationships between entities in a database that can't be represented by a foreign key, but can be by a general join condition. A simple example might be items in an order which you could derive from a foreign key, compared to items in an order which have sales tax which is something more general. So whatever the result of this discussion, don't just consider foreign keys, think bigger. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] How to analyze load average ?
On Mon, Aug 06, 2012 at 08:06:05PM +0300, Condor wrote: I think load avg is high because before I change the servers my produce server was on 16 cpu, 24 gb memory and load avg on that server was 0.24. Database is the same, Our monitoring system starts worrying about the load average if it ever goes above 0.75*number of cores. In your example it looks a bit like you paid for 15 more cores than necessary. Especially at the lower end you have to take the load with a large grain of salt. Lots of short running processes (like a make run) while make the load fluctuate. But even things like it taking a while for your disk cache to reach steady state after a reboot can mean that you see a higher than normal load for a while. But 0.88 is really nothing to worry about. Perhaps it is just slower core or a slower memory bus. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Locking or Something Else?
On Sun, May 20, 2012 at 12:26:26AM -0700, Ian Harding wrote: I have a situation where an increase in volume of inserts into the main transaction table causes a huge slowdown. The table has lots of indexes and foreign keys and a trigger. Clearly, something is causing a resource contention issue, but here's my main question: I have log_lock_waits = on and deadlock_timeout = 250ms. Is there any way I could have delays of tens of seconds caused by a write conflict in a table accessed by a trigger that would not result in the wait being logged? The most common cause for slowdowns during inserts is if you're not wrapping them into large transactions. The deadlock timeout only tracks deadlocks, it won't trigger on normal lock waits. There can be issues with respect to foreign keys, but I didn't think they are triggered on inserts. If you are wrapping into transactions, then it may be that your disk subsystem has slow fsyncs. Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Libpq question
On Sun, May 20, 2012 at 02:51:42PM +0200, zeljko wrote: John Townsend wrote: It appears that some developers (Davart) are by-passing the standard client library, ???libpq.dll???, and directly accessing the server using Delphi or FPC. I am not sure of the advantage here. All libpq.dll I'm FPC user and I use libpq.so(.dll,.dylib) via zeoslib. Those who bypass libpq probably uses odbc connections or similar. The PostgreSQL-ODBC drivers that I know of use libpq as well. There are other implementations of of the libpq protocol, the Java lib being the major one. There are pure perl/python implementations but AFAIK they are not widely used. It's not common to not use libpq. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Global Named Prepared Statements
On Tue, May 15, 2012 at 05:38:27AM +0530, Samba wrote: Hi, Does postgresql support Global Prepared Statements, which are prepared only once per server and not per every connection? As pointed out, no. Problem with per-connection prepared statements is that the onus of preparing those statements for each connection lies with the client which makes those connections. Ideally, the performance of an application must be decided by the Server that hosts and not by the client that uses the service. How is this different from using CREATE FUNCTION to create a function which has the desired effect? This is a well understood and commonly used paradigm. When using a connection pooler any query plan caching will happen automatically. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] SQLSTATE XX000 Internal Error 7
On Thu, May 03, 2012 at 02:46:16PM +0800, Ben Madin wrote: (PS How did you come to deciding that it was EINVAL - is that 'Error INVALid argument'?) It's one of the standard error codes, see for example http://www.jbox.dk/sanos/source/include/errno.h.html Tom is right, it's not clear how this error can occur. Linux does it if you ask for O_DIRECT on a filesystem that doesn't support it, but it doesn't look like that's the problem here either. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] pqlib garbage collection
On Thu, May 03, 2012 at 08:33:06AM +0200, Alexander Reichstadt wrote: Hi, since I got no answer so far I searched through the docu again. I searched for GC as well as Garbage, and all garbage refers to is with regard to vacuuming a database. But my question refers to wether or not memory management is with garbage collection supported or not. When I try to link against pqlib, it seems I need to have garbage collection enabled in order for it to link. But the documentation mentions it nowhere. (I'm assuming you're referring to libpq, the C library). There is no magic garbage collection. You must use PQclear. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Updates, ShareLocks, RowExclusiveLocks, and deadlocks
On Wed, Apr 25, 2012 at 01:12:37PM -0600, Ben Chobot wrote: So, if I understand what you're saying, if I have two connections each transactionally updating many rows, then each transaction will need to acquire a RowExclusiveLock for each row (as documented), and also (as not documented?) each acquisition will temporarily acquire a ShareLock on the other transaction's transactionid? That seems to fit what I'm seeing in pg_locks, and I suppose if there is an overlap in rows between to two transactions, and if those updates happened in the wrong order, then we'd get deadlock. I just assumed we'd see that in the logs as deadlocks due to waiting for RowExclusiveLocks, while it sounds like you are saying the log will show them as ShareLocks? I think what you're missing here is that RowExclusiveLocks are taken by marking the row itself. If two transactions want to lock the same row, transaction A marks the row, then transactions B sees the marking and so must wait until transaction A completes. To do this transaction B tries to take a lock on the transaction A. Since each transaction has an exclusive lock on itself, the effect is that transaction B waits for transaction A to complete. Apparently this is done using a ShareLock, but I think any locktype would do. But taking a lock on another transaction is a pretty common way to wait on another transaction. And these locks only appear when needed. If that's the case, would doing the updates in, say, primary key order solve this problem? I'm pretty sure we're just pulling things out of the queue and running them in random order. If you're taking locks it's always better to be consistant about the order, so it may help, yes. If that's not the case, then what information would be helpful in understanding what's going on? All of pg_locks or just the locks related to the virtualtransactionid of the update with the SharedLock? There are no foreign keys related to this table. Updating a row locks it against other updates, because the second update needs to know which version of the row it's updating. Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Large Databases redux
On Wed, Mar 21, 2012 at 02:58:43PM -0700, John R Pierce wrote: On 03/21/12 2:18 PM, Jason Herr wrote: I have my own theories based on what I've read and my puttering. I think I can get away with a disk for the OS, disk for the WAL, disk for the large table (tablespaces) and a disk for the rest. And when I say disk I mean storage device. I'm thinking RAID1 15k disks for each set but the databases and then raid 10 or VERY large disks. I think you're better off just making one big raid10 out of all the disks and putting everything on it, maybe in different file systems to seperate out file fragmentation. this way the IO workload is evenly distributed across all the disks. That, and a good RAID controller with BBU cache will go a long way to relieving the pain of fsync. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Interesting article, Facebook woes using MySQL
On Tue, Mar 13, 2012 at 01:22:18AM +0100, Stefan Keller wrote: Hi all 2011/7/12 Chris Travers chris.trav...@gmail.com: I am not convinced that VoltDB is a magic bullet either. I don't I have the chance to help preparing an interview with Mike Stonebreaker to be published at www.odbms.org I'd really like to know, if he is up-to-date how Postgres performs these days and how he thinks how VoltDB overcame the overhead he claims to exist in old elephants. Do you all have more questions to Mike? I'm curious what he thinks about the role of the optimiser. IME postgresql wins for my workloads simply because PostgreSQL is smart enough to perform the joins in the right order and use the right indexes. MySQL seems to have some heuristics which are wrong just often enough to be irritating. Oh yeah, and it doesn't have CREATE INDEX CONCURRENTLY, that's *really* annoying. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] pg_dump -s dumps data?!
On Mon, Jan 30, 2012 at 11:18:31PM -0500, Tom Lane wrote: I don't recall that we thought very hard about what should happen when pg_dump switches are used to produce a selective dump, but ISTM reasonable that if it's user data then it should be dumped only if data in a regular user table would be. So I agree it's pretty broken that pg_dump -t foo will dump data belonging to a config table not selected by the -t switch. I think this should be changed in both HEAD and 9.1 (note that HEAD will presumably return to the 9.1 behavior once that --exclude-table-data patch gets fixed). Perhaps a better way of dealing with this is providing a way of dumping extensions explicitly. Then you could say: pg_dump --extension=postgis -s to get the data. And you can use all the normal pg_dump options for controlling the output. The flag currently used to seperate the table schema from the table content could then interact logically. Another way perhaps: pg_dump --extension-postgis=data-only pg_dump --extension-postgis=schema pg_dump --extension-postgis=all pg_dump --extension-postgis=none The last being the default. Just throwing out some completely different ideas. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Strange problem with create table as select * from table;
On Fri, Nov 04, 2011 at 09:04:02PM -0400, Tom Lane wrote: Hah ... I have a theory. I will bet that you recently added some column(s) to the source table using ALTER TABLE ADD COLUMN and no default value, so that the added columns were nulls and no table rewrite happened. And that these troublesome rows predate that addition, but contained no nulls before that. And that they are the only rows that, in addition to the above conditions, contain data fields wide enough to require out-of-line toasting. These conditions together are enough to break the assumption in toast_insert_or_update that the old and new tuples must have the same value of t_hoff. Wow! Good catch. This is trivial to fix, now that we know there's a problem --- the function is only using that assumption to save itself a couple lines of code. Penny wise, pound foolish :-( No doubt the assumption was true when the code was written, but still. Hve a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] select where not exists returning multiple rows?
On Wed, Nov 02, 2011 at 07:22:09AM -0400, Chris Dumoulin wrote: And we're doing an insert like this: INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$4) In this case $1 and $4 should always be the same. FWIW, If they're always going to be the same, you can put that it the query, like so: INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$1) Saves a parameter. I don't see how it's possible to get duplicate rows here, unless maybe the select where not exists is somehow returning multiple rows. Any ideas what's going on here? As pointed out by others, you don't say if it this is a race condition between processes or if it always does this. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] PostGIS in a commercial project
On Tue, Oct 25, 2011 at 01:41:17PM +0200, Thomas Kellerer wrote: Thank you very much for the detailed explanation. I always have a hard time to understand the GPL especially the dividing line between using, linkin and creating a derived work. That because the GPL does not get to define those terms. They are defined by copyright law, the licence does not get to choose what is a derived work and what isn't. The FSF is of the opinion that anything linked to a GPL library is a derived work, but that isn't true in all cases (libedit vs libreadline is one of those borderline cases). I note in the OPs case they are relying on the customer to install PostGIS. The GPL only applies to *redistribution* not usage. So if you're not supplying your customers with PostGIS then the fact that it's GPL seems completely irrelevent. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Video of Activity on PostgreSQL GIT repository
On Thu, Oct 13, 2011 at 11:55:47AM -0300, Fabrízio de Royes Mello wrote: Hi all, I like to share this cool video which I build [1] (using gource [2]) to show the activity of PostgreSQL GIT repository in the few months ago. That's pretty cool. I'm always astonished at the code turnover rate in postgres, it's really a very active project. [1] http://www.youtube.com/watch?v=gzTBJW2EVJY [2] code.google.com/???p/???gource/ [3] github.com/???postgres/???postgres Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] (another ;-)) PostgreSQL-derived project ...
On Sun, Sep 25, 2011 at 06:11:36AM +, Albretch Mueller wrote: ~ Well, at least I thought you would tell me where the postgresql-base is to be found. The last version I found is: ~ http://freebsd.csie.nctu.edu.tw/pub/distfiles/postgresql/postgresql-base-8.3beta2.tar.bz2 Notwithstanding the rest of your post, I'm surpised you missed the website: http://www.postgresql.org/download/ There's a source code link, as well as several others. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Parameterized prepared statements
On Wed, Aug 31, 2011 at 09:44:09AM +0800, Craig Ringer wrote: Things like pre-parsed prepared statements that're re-planned on every execution are often proposed as solutions to this. This has me wondering: rather than expensively re-planning from scratch, would it be possiblet to adjust the planning process so that *multiple* alternative plans would be cached for a query, using placeholders for unknown rowcounts and costs? At execution, the unknown costs would be filled in and the plans compared then the best plan picked for this execution. Is this crazy talk, or could it significantly reduce the cost of re-planning parameterized prepared statements to the point where it'd be worth doing by default? The problem is that the number of alternate plans is enourmous (combinatorial). You need something to prune the results and that's where the stats come in. What you need is some way of reducing the number of plans while keeping the useful ones. For example, an equality on a primary key column is going to return one result, no matter what the parameter. But for other things it gets really hard. It could be done, but I'm not sure if the payoff is worth it. On an unrelated note, does Pg do any kind of smart searching on `IN' lists, or just a linear scan? Would it be worth sorting longer IN list results so each iteration could do a binary search of the list? I beleive large INs can be turned into hash lookups, but I'm not sure. Try a query with 10,000 element in an IN and see what happens. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Selecting all records which are in upper case
On Sat, Aug 27, 2011 at 03:12:44PM +0530, Amitabh Kant wrote: Hello I have a simple table 'location' : id - Int (associated with a sequence) name - Character varying (100) I have to delete all records where values in name field are all in upper case. For example, if the test data is as follows: Might not work if you have non-ascii characters (but your example code breaks there too), but what about: DELETE ... WHERE upper(name) = name; Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Getting value of bind variables
On Tue, Aug 23, 2011 at 09:07:20AM +0530, Jayadevan M wrote: I guess so. But when I tried the same query on psql by replacing ($4) with a value like '20110404', the query works OK. The value of $4 is being passed from a java application. So does this mean I have to change the data type in java code? For clarity, when you put the value '20110404' in a query, that is not a varchar. Rather it's type is coerced to whatever is most appropriate from the surrounding SQL. To generate the same error you need to put '20110404'::varchar in place of the parameter. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] string comparison problem
On Mon, Aug 01, 2011 at 10:53:16PM +0300, Johnny Edge wrote: Hi folks, I have a problem with identical versions of postgresql - 8.3.13 Config files are identical. HostA is a 32 bit CentOS 5.3 install and the hostB is x86_64 CentOS 6. Difference in architecture should not be a problem - I have another 64 bit CentOS where both queries are executed displaying identical results. It's probably locale related. Postgres uses the same order as the sort command. Try show lc_collate. Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Referencing function value inside CASE..WHEN
On Sun, Jun 19, 2011 at 10:45:12AM +0800, lover boi wrote: In the following statement, does the custom function get executed twice? SELECT MY_FUNCTION(...), CASE WHEN MY_FUNCTION(...) = '...' THEN '...' ELSE '...' END Yes If so, is there a way I can make it execute once? I tried this but it gave me a Column my_function does not exist error. SELECT MY_FUNCTION(...) AS my_function, CASE WHEN my_function = '...' THEN '...' ELSE '...' END You almost had it right, there is another syntax for CASE: CASE MY_FUNCTION(...) WHEN 'foo' THEN ... WHEN 'bar' THEN ... ELSE ... END; http://www.postgresql.org/docs/8.4/static/functions-conditional.html Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] Short-circuit boolean evaluation
On Sat, Apr 30, 2011 at 10:34:32AM -0400, David Johnston wrote: No. It will not be called Or No. Postgresql does not short-circuit boolean evaluations ? SQL is a somewhat declarative language. There is no order to evaluation as such. So you can't talk about short circuiting either. This applies to any SQL database. You can somewhat enforce order with subselects and CASE and other such constructs. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] Huge spikes in number of connections doing PARSE
On Mon, Mar 14, 2011 at 07:49:46PM +0100, hubert depesz lubaczewski wrote: These 60 were summarized, and output is available here: http://www.depesz.com/various/locks.summary.txt as you can seem, in 48 cases backend process was in semop(), which relates directly to my previous findings with ps/wchan. It's unfortunate you don't have debug symbols enabled, which makes these traces somewhat unreliable. So you get odd things like index_open calling index_close. The common factor seems to be lots of index locks. Do you have very many indexes? Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] Why count(*) doest use index?
On Sun, Mar 06, 2011 at 11:03:23AM +0300, Allan Kamau wrote: I would assume the primary key or unique indexes are the cornerstone of each insertion and deletion. snip Or am I missing a crucial point. The real issue is that you can have four programs all doing count(*) and all getting different answers. How? Because what you see is dependant on what snapshot of the database you're looking at. And information about what snapshot can see what tuple is stored in the table. An index does not have enough information to work this out. The DBs that don't have this issue are usually like MyISAM, no transactions so no issues about different snapshots. And crappy concurrency. As soon as you go to more advanced systems the easy option falls away. For example http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/ If it's really really important there are ways you can use trigger tables and summary views to achieve the results you want. Except it's expensive and when people are told that all of the sudden the count(*) performance isn't so important any more. :) The other option is visibility data in the index. Doubles the size of your indexes though. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] updating all records of a table
On Sat, Mar 05, 2011 at 07:38:23AM -0800, ray wrote: This has been a great thread! I am missing something because I do not know what CTAS is. WOuld someone please help me understand. Create Table As Select. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] Thoroughly confused about time zones
On Mon, Feb 28, 2011 at 10:06:28AM -0500, Rob Richardson wrote: Our application stores the times at which several events happened, and we need to be able to calculate the elapsed time between events. Currently, the times are stored as timestamps without time zone, in both local and UTC times. Elapsed time calculations are based on the UTC times. Supposedly, that should avoid problems posed by the change from standard to daylight savings time, but it is not working out that easily in practice. A useful way I find of thinking about it is: you have two things you want to be able to store. - An instant in time, an event for example. The representation of this instant is dependant on where you are. This is the timestamp with time zone. - The wall clock time, what it say on the wall. So, no time zone, it just represents what a clock said at some point. This is the timestamp without time zone. The latter is usually not that useful, except for output. What you usually want is the timestamptz. Hop this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] PostgreSQL 9.0.3 - collate/ctype order by hu_HU.UTF8 is not correct sorting
On Sun, Feb 06, 2011 at 11:02:25AM +0100, Adam PAPAI wrote: I've tested it with 8.4 and 9.0 with locale=hu_HU.ISO8859-2, encoding=LATIN2, It's working correctly. But not with locale=hu_HU.UTF-8, encoding=UTF-8 Is it related to the FreeBSD team or the PostgreSQL team? Last I checked *BSD did not support sorting in UTF-8. I know Apple added it themselves because they needed it but I don't think it got backported to *BSD. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] Database Design Question
On Wed, Feb 02, 2011 at 11:44:51AM -0800, John R Pierce wrote: On 02/02/11 11:24 AM, Joshua D. Drake wrote: Forget separate databases. Use separate users with schemas. for canned applications like mediawiki and phpbb? not sure they support that. If they use different users you can easily do it by setting the default search path per user. ALTER USER phpbb SET search_path='phpbbschema'; As long as the apps don't play with the search path themselves it should be fine. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] error while trying to change the database encoding on a database
On Mon, Jan 24, 2011 at 12:16:46PM -0500, Geoffrey Myers wrote: We hope to identify the characters and fix them in the existing database, then convert. It appears to be very limited, but it would help if there was some way to identify these characters outside of simply doing the reload of the data and finding the errors. Hence the reason I asked about a resource that might identify the characters. Short answer, any byte with the high bit set. You're going to need to assign them a meaning. Additionally you're going to have to fix your code to only output correct encoded data. The suggestion to simply reload the database as if all the current data was WIN1251 or Latin-9 is a fairly easy way to getting the database into a reasonable format. The data would have to be checked though. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] Implementing replace function
On Tue, Nov 02, 2010 at 10:46:42AM +0100, Matthieu Huin wrote: Hello, If you expect your data to reach some kind of critical size at some point ( ie updates will be more likely than inserts at that point ), you can optimize your UPSERT code by trying to UPDATE before INSERTing. Otherwise trying to INSERT first should decrease the average UPSERT execution time in the long run, since you are less likely to hit the exception and do some extra work on the table. You'd almost think of using some kind of branch prediction techniques. You could track what happened the last two times and use that to predict which would be better. There's always pathelogical cases, but it could work well for normal workloads. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] Feature request for this mail list
On Tue, Nov 02, 2010 at 11:58:35AM -0400, zhong ming wu wrote: I looked on PG website but couldn't find admin email for this list I would like list emails to have extra 'reply-to' header addressed to the list Is it too much to ask? When replying from a mobile client it can be tricky without + even from a bigger client most often I forgot The mailing list manager has several options which may be relevent here. There is a reply-to option which may do what you want: http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
[GENERAL] gitweb error?
When I go to the following link: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=e6721c6e1617a0fc8b4bce8eacba8b5a381f1f21 I get the following error: XML Parsing Error: undefined entity Location: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=e6721c6e1617a0fc8b4bce8eacba8b5a381f1f21 Line Number 53, Column 4: Trynbsp;tonbsp; ... ^ Is it just my browser being pedantic (Firefox 3.6) or something else? Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] Record Separator with psql -c when output to a variable not a file!
On Mon, Oct 04, 2010 at 12:51:11PM -0700, andrew harvey wrote: The default psql -c record separator is a newline when output is the screen, console or a file. But what if I'm required to send output to a variable and not to a file (or standard output?) command=`psql -c SELECT * FROM pg_stat_database` when you retain the query result within the variable $command you need to count all the fields in the entire output in order to select the particular one that you want. (because the record separator newline doesn't apply to within the variable named above) Therefore all the sql output is bundled up into one string with very, very many fields. Looks at the options to psql, for example you have the -A and -t options which (IIRC) suppress the output of the header and the extraneous spacing. You can control the delimiter also. It so happened that field 38 was the number of pages served from disk for one of my databases and field 53 turned out to be the number of pages served from cache for another one of my databases. But this is hardly a sensible way of producing results! If you want a particular column, select only that column instead of SELECT *. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] Incrementally Updated Backups
On Sun, Sep 12, 2010 at 12:18:10PM +0200, J. Roeleveld wrote: How can you ensure the snapshot is in a consistent state if the server is running? If a snapshot is taken between 2 updates in a single transaction, only half of this transaction is included in the snapshot. I would never take an LVM (or similar) snapshot of an application that can't be paused in a way to provide a consistent filesystem. That's the trick, the filesystem is always in a consistant state, otherwise how could a database survive a power failure? The trick is WAL, which ensure that changes are logged consistantly and replays them if the database crashes. If you take a snapshot the database will simply startup and replay the log as if the machine crashed at the point. All committed transactions appears anything uncommitted vanishes. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] postgres.conf settings
On Sun, Aug 29, 2010 at 06:02:14PM +0100, Dario Beraldi wrote: Hello, I'm trying to tune the parameters in postgres.conf to improve the performance of my database (where performance = 'make queries run faster'). I would be very grateful if you could give me some comments about my choice of configuration settings below (did I do anything very silly? Am I missing something relevant?). Not a bad start, but to be sure you really need to provide a bit more info, like: - How many simultaneous clients you're expecting? - Lots of updates, or is it read only? - Lots of simple queries, or fewer but more complex queries? Basically, what's the workload? Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] C-Functions using SPI - Missing Magic Block
On Sat, Jul 03, 2010 at 09:35:56AM +0800, Craig Ringer wrote: On 02/07/10 21:26, saitenhe...@web.de wrote: #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif Why the conditional compilation of PG_MODULE_MAGIC? That's the recommendation, so the module compiles on all versions of Postgres. ERROR: incompatible library G:/PostgreSQL/8.3/lib/pgExampleSPI.dll: missing magic block TIP: Extension libraries are required to use the PG_MODULE_MAGIC macro. My guess is that the installed server headers are not compatable with th eversion of postgresql installed. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] checkpoint spikes
On Thu, Jun 10, 2010 at 04:00:54PM -0400, Greg Smith wrote: 5. Does anybody know if I can set dirty_background_ratio to 0.5? As we have 12 GB RAM and rather slow disks 0,5% would result in a maximum of 61MB dirty pages. Nope. Linux has absolutely terrible controls for this critical performance parameter. The sort of multi-second spikes you're seeing are extremely common and very difficult to get rid of. Another relevent parameter is /proc/sys/vm/dirty_writeback_centisecs. By default linux only wakes up once every 5 seconds to check if there is stuff to write out. I have found that reducing this tends to smooth out bursty spikes. However, see: http://www.westnet.com/~gsmith/content/linux-pdflush.htm which indicates that kernel may try to defeat you here... Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] Persistence problem
On Thu, May 13, 2010 at 12:04:56PM +0200, I. B. wrote:
I'll try to explain with as less code as possible.
One of the types I wanted to create is called mpoint. This is a part of code:
snip
typedef struct {
int4 length;
int noOfUnits;
void *units; // this is later casted to uPoint *
} mapping_t;
This is not the correct way to handle varlena types. You can create the
datum that way, but if PostgreSQL decides to compress it (as it may
when writing to disk) you won't be able to read it back. Notably, the
length part of a varlena type is not always 4 bytes.
Make sure you have fully understood this page:
http://www.postgresql.org/docs/8.4/static/xfunc-c.html
it has a number of examples dealing with variable length types. You
MUST use the VARDATA/VARATT/etc macros to construct and read your data.
Hope this helps,
--
Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/
Patriotism is when love of your own people comes first; nationalism,
when hate for people other than your own comes first.
- Charles de Gaulle
signature.asc
Description: Digital signature
Re: [GENERAL] Persistence problem
On Wed, May 12, 2010 at 07:12:10PM +0200, I. B. wrote: That was my first guess. I used palloc everywhere.. But to be sure, after I made the type, I tried to do the something like: mytype * result; mytype * realResult; result = createType(...); realResult = (mytype *)palloc(mytype-length); mempcy (realResult, result, result-length); Did you define the type properly at SQL level? Is it a varlena type or fixed length? Did you return it properly (as Datum)? You're going to need to post more information before we can help you usefully. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] When is an explicit cast necessary?
On Sat, Apr 10, 2010 at 05:15:18PM +, Alan Millington wrote: In C, if I declare a short variable shortvar, I can write an assignment shortvar = 1. The literal value 1 is an int, but the compiler will implicitly convert it to a short. Similarly, if I write a function func() which takes a short argument, then provided I use function prototypes, I can write a call func(1). Again the compiler will implicitly convert the int 1 to a short. The problem is basically that postgres sees the 1 not as a literal but as an integer, which can't be downcast to a smallint. If however you wrote the literal as '1' (with quotes) postgres would happily downcast it for you without any problem. The question is: does the column really need to be smallint. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] Internal PG functions, how to pass proper parameters?
On Wed, Apr 07, 2010 at 10:30:35AM +0200, ?ukasz Dejneka wrote: Hi all I need a bit assistance. In ../src/backend/utils/adt/tsginidx.c there is a function called gin_extract_tsquery. snip The above function call is incompleate (only 2 params instead of 5) but it's not relevant at this point. The issue is that no matter what I pass (Datum, pointer to Datum, pointer to char, pointer to int32, PG macros to variables and pointers, etc...) as a 2nd parameter I get an error when I call my wrapper function from Postgres. It goes well right until the line before step: 04 notice in gin_extract_tsquery. Namely the *nentries = 0; line. This may be silly, but did you declare your function to be a V1 function? Also a related question: Is it possible to launch a function like gin_extract_query directly from Postgres? I'ts decralation is: CREATE OR REPLACE FUNCTION gin_extract_tsquery(tsquery, internal, smallint, internal, internal) RETURNS internal AS I've tried to find something about internal parameters in functions in manual but failed. If it's possible, what would be a working example? internal usually means a pointer to something you can't make from SQL. So you might be able to declare the function, but in no way could you actually call it successfully. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] Yikes: ERROR: out of memory
On Sun, Mar 14, 2010 at 08:11:01PM -0400, Carlo Stonebanks wrote: Now THIS is a new one for me! I have no idea where to even start. Does anyone know how to look for the error? Below is the query and what I believe are the related log entries. Any help will be rewarded with heartfelt gratitude and praise, or you can just come to Montreal and hit me up for a beer. First, how much memory have you got? ERROR: out of memory SQL state: 53200 Detail: Failed on request of size 134217728. That's 128MB HashBatchContext 66222872 total in 77 blocks; 4824944 free (75 chunks); 561397928 used HashBatchContext 0847768 total in 16 blocks; 3739736 free (9 chunks); 47108032 used HashBatchContext 35258136 total in 26 blocks; 4958688 free (24 chunks); 130299448 used HashBatchContext 20192792 total in 36 blocks; 7649816 free (29 chunks); 212542976 used TupleSort: 369090584 total in 46 blocks; 7648 free (25 chunks); 369082936 used That's a few hundred MB also. I'd suggest checking your work_mem settings to see if you havn't gotten too much configured. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] DROP column: documentation unclear
On Mon, Mar 08, 2010 at 05:09:14PM +0100, Adrian von Bidder wrote: Hi, The documentation about DROP COLUMN is a bit unclear: | The DROP COLUMN form does not physically remove the column, but simply | makes it invisible to SQL operations. Subsequent insert and update | operations in the table will store a null value for the column. Thus, | dropping a column is quick but it will not immediately reduce the on-disk | size of your table, as the space occupied by the dropped column is not | reclaimed. The space will be reclaimed over time as existing rows are | updated. subsequent ... will store a null value would imply that deleted columns will still take some place, while the space will be reclaimed ... would suggest that new rows (insert or updates in mvcc) don't have the deleted column anymore - I'm not quite sure how to interpret this. What is pg doing? What you're missing is that in postgres NULLs are stored as a bit in the header and there is no data. So in a sense NULLs take no space (well, one bit) which means both statements are true. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
[GENERAL] timestamp literal out of line
Version: 8.3.9 I was surprised when I came across the following while changing some code to send parameters out of line (to avoid interpolation). postgres=# prepare test1 as select timestamp '2009-01-01'; PREPARE postgres=# prepare test2 as select timestamp $1; ERROR: syntax error at or near $1 The workaround is simple, use a cast instead, but is there a particular reason why you can't use a parameter there? This does work, oddly enough. postgres=# prepare test2 as select timestamp '2009-01-01' at time zone $1; PREPARE Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Putting index entries to XLog
I didn't see an answer to this, so I thought I'd point out that you do
not need to do anything with XLOG to make a functional index. It is
only necessary when you want to make it crash-safe or work over SR.
But when you're building a new index type, making it crash safe is one
of the last things on the list. So unless you're really far I'd suggest
dropping everything XLog realted and coming back to it later.
Have anice day,
On Thu, Feb 25, 2010 at 12:41:36PM +0100, Carsten Kropf wrote:
Hi *,
I have encountered a problem while implementing an index structure. I don't
have any access to the RM (I don't know, how to set it up properly) using my
index. However, when I don't have the situation of working with a temporary
table, I need to put entries using XLogInsert(...). The problem for me is,
that based on the fact, that I don't have the access to the RM, I can't put
proper entries to the xlog facility. How can I achieve that my index
structure can be stored properly at least?
I'm getting lots of errors, when I just put a XLogRecPtr with data {1,1} as
LSN of the page. On the other hand when doing XLogInsert calls to work with
the component properly I don't know which data to put in there. When I'm
using some kind of RM_ID (like RM_GIST_ID or something like that) I'm getting
problems because I don't know which code to insert there. I decided to take
RM_GIST_ID and an invalid code (so it won't call any of the xlog gist
implementations upon recovery) which, however failed, too. Unfortunately my
task is to implement a certain structure with PostgreSQL, so all of my
current work actually depend on this action.
I hope that somebody will be able to help me according to this issue.
Thanks in advance
Best regards
Carsten Kropf
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
signature.asc
Description: Digital signature
Re: [GENERAL] Performance comparison
On Wed, Feb 24, 2010 at 09:13:36PM -0500, Greg Smith wrote: Martijn van Oosterhout wrote: I remember a while back someone posted a graphs showing a scalability of postgresql for various versions (I think 8.0 to 8.4). I've tried to find this image again but havn't been able to locate it. Does anyone here remember? http://suckit.blog.hu/2009/09/29/postgresql_history Yes, that's the one, thank you. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
[GENERAL] Performance comparison
Hoi, I remember a while back someone posted a graphs showing a scalability of postgresql for various versions (I think 8.0 to 8.4). I've tried to find this image again but havn't been able to locate it. Does anyone here remember? Mvg, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Week numbers and calculating weekly statistics/diagrams
On Tue, Feb 16, 2010 at 01:14:26PM +0100, Alexander Farber wrote: Does anybody has an advice how to save the week number? If I save it as a timestamp then calculating realtime statistics (on a player profile click) will probably be CPU-intensive, because I have to calculate the week numbers each time. You should probably seperate the storage from the representation. The easiest way of storing the information of a week is the date of the first day (after all, a week could begin on a sunday or monday, depending on your point of view). This will make grouping and searching quick, as it's just an integer. If you really wanted to you could choose an epoch and count weeks from there but I doubt that's worth the effort. As for how you represent it to the users, you'll have to create some conversion routine for output, but I seriously doubt that's going to be a bottleneck. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Memory Usage and OpenBSD
On Tue, Feb 09, 2010 at 08:19:51PM +0500, Anton Maksimenkov wrote: It means that on openbsd i386 we have about 2,2G of virtual space for malloc, shm*. So, postgres will use that space. But mmap() use random addresses. So when you get big chunk of memory for shared buffers (say, 2G) then you may get it somewhere in middle of virtual space (2,2G). This is essentially the reason why it's not a good idea to use really large amounts of shared_buffers on 32-bit systems: there isn't the address space to support it. Can anybody briefly explain me how one postgres process allocate memory for it needs? I mean, what is the biggest size of malloc() it may want? How many such chunks? What is the average size of allocations? There's no real maximum, as it depends on the exact usage. However, in general postgres tries to keep below the values in work_mem and maintainence_workmem. Most of the allocations are quite small, but postgresql has an internal allocator which means that the system only sees relatively large allocations. The majority will be in the order of tens of kilobytes I suspect. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Error invalid byte sequence for encoding UTF8 on insert into BYTEA column
On Thu, Jan 21, 2010 at 11:54:32AM -0800, Alan Millington wrote: Today for the first time since upgrading to Postgres 8.4.1 I tried out part of the code which inserts some binary data into a table. The insert failed with the error invalid byte sequence for encoding UTF8. That is odd, because the column into which the insert was made is of type bytea: the data is meant to be binary data, not UTF8-encoded Unicode. Inserting in bytea needs an extra level of escaping when the parameters are sent inline. See http://www.postgresql.org/docs/8.4/interactive/datatype-binary.html When I was using Postgres 8.1.4 the same code worked. My code, the mxODBC code and the driver are all unaltered: only the Postgres version has changed.. Is there something I can tweak to get it to work on Postgres 8.4.1? Very odd. The syntax for this hasn't changed in a long time. And I would have thought you'd be sending your paramters out of line anyway. Can you check that? Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Collate order on Mac OS X, text with diacritics in UTF-8
On Sat, Jan 16, 2010 at 09:10:53PM +, Greg Stark wrote: Switching to ICU means trading our current inconsistency from platform to platform for a different inconsistency which would be better in some cases and worse in others. Or, you can have the cake and eat it too. That is, aim for the end goal and let people choose what library they want to use for sorting (that is, extend the meaning of the locale identifier). Patches for this should be in the archives somewhere. As I recall the reason this was rejected is that *BSD lack the capability of handling multiple collation algorithms at all at the libc level (that is, if you don't just tell people to use ICU in that case). Mac OS X doesn't have great POSIX locale support but at least they implemented strcoll_l. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] vacuum issues under load?
On Fri, Jan 15, 2010 at 08:41:38AM -0700, Scott Marlowe wrote: With slony 2.0.3 or so, I had occasional complete lockups of my database that I didn't have time to troubleshoot as it was a live cluster and I had to restart slony and the databases to get them back up and running. With slony 2.0.2 I had similar problems. A CREATE INDEX on the slave caused slony on the slave to block while inserting into the table which eventually blocked the server during the log switch (TRUNCATE) which eventually blocked everything else. It occurs to me that the slony daemon should be able to get the TRUNCATE command to abort if it takes too long. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Collate order on Mac OS X, text with diacritics in UTF-8
On Wed, Jan 13, 2010 at 04:15:06PM +0100, Martin Flahault wrote: [postgres] newbase=# select * from t1 order by contenu; contenu - A E a e Postgresql outputs whatever the C library does on the underlying system. The quality of this varies wildly. à As with others DBMS (MySQL for example), diacritics should be ignored when determining the sort order. Here is the expected output: MySQL implements the unicode collation algorithm, which means it essentially does what you want. It seems there is a problem with the collating order on BSD systems with diacritics using UTF8. Last I checked, BSD did not support useful sorting on UTF-8 at all, so it's not surprised it doesn't work. in a UTF8 text file and use the sort command on it, you will have the same wrong output as with PostgreSQL : Yes, that's the basic idea. Mac OS X apparently provides ICU underneath for programs that would like true unicode collation, but there is little chance that postgresql will ever use this. Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Charset Win1250 on Windows and Ubuntu
On Mon, Dec 21, 2009 at 10:26:51AM +0100, Durumdara wrote: So if I have Python and pygresql, can I set this value in Python? The main problem that I don't want to set this value globally - possible another applications want to use another encoding... Each connection can set the encoding to whatever they like. Something I find useful is to setup the DB as UTF-8 but then do: ALTER DATABASE foo SET client_encoding = latin9; which sets the default for the DB, or ALTER USER bar SET client_encoding = latin9; Which lets you set the defauts for each user. This means that old scripts can work unchanged but newer scripts can choose UTF-8 if they want it. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Question about the ANY operator
On Mon, Dec 21, 2009 at 02:46:59AM -0800, Mike Christensen wrote: I'm having trouble figuring out the ANY operator.. Basically I want to return rows that match any of the given IDs: select Name from Users where UserId = ANY ARRAY['948aeda5--41bd-af4e-71d1c740db76', '5ee315ea-7ef6-4fa5-809a-dc9931a01ed1']::uuid[]; The syntax is '= ANY(foo)', you're missing the parenthesis. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] PlPerl scope issue
On Wed, Dec 16, 2009 at 03:15:21PM -0600, Peter wrote:
Hello Tim,
Thanks for the reply! I'm still not sure why it's bad to have named
subroutines. At any rate I cant use anon subs since we have a complicated
reporting subsystem that relies on Perl formulas being eval-ed at runtime,
and these refer to various subroutines.
Maybe the example below will clear things up for you. I don't
understand why you could use anon subs, since they're not a lot of
difference between sub foo {} and $foo = sub {} except the latter
doesn't have the problem you're running into.
sub main
{
my $test=shift;
test();
return $test;
sub test {
print X=.$test.\n;
}
}
main(1);
main(2);
Output:
X=1
X=1
Have a nice day,
--
Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
signature.asc
Description: Digital signature
