[GENERAL] Will modifications to unlogged tables also be flused to disk?

Hi, I would like to use postgresql's unlogged tables on an embedded system to avoid frequent writes to flash memory. While documentation clearly states that unlogged tables don't have to go through the WAL, it doesn't mention what happens to the data when it is written directly to the database.

Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?

Hi Andreas, They will lost after a crash, but after a regular shutdown / restart all data in the table. Yes, the semantics are clearly stated in the documentation. What I wonder is whether postgresql will issue flush/fsync operations when unlogged tables are modified? Regards, Clemens --

Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?

Hi Vik, Yes. The only difference between logged and unlogged tables is the lack of WAL. As long as there's no crash, unlogged tables are treated the same as logged tables as far as flushing/fsync-ing is concerned. Ok thats really bad news :/ After reading the discussion about calling

Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?

Hi Andres, No fsyncs will be issued in the common paths, and they won't be written to disk by !shutdown checkpoints. But they *will* be written to disk if there's not enough space in shared_buffers for all the data. With unlogged tables I still see data written to the device every 10 seconds,

Re: [GENERAL] Why is wal_writer_delay limited to 10s?

Hi, Is there any technical reason why wal_writer_delay is limited to 10s? I am using postgresql in an embedded system and writing every 10s burns flash at an amazing rate (a 32GB SDHC card with ext4 survived 4 days with only a few GB written). I've now switched to nilfs2 which is way more

Re: [GENERAL] Why is wal_writer_delay limited to 10s?

Hi Peter, Thanks a lot for your reply. What Postgres version? The WAL Writer will hibernate on Postgres 9.2+. walwriter.c says: I am using Postgresql-9.1 shipped with Raspbian (debian for raspberry pi). /* * Number of do-nothing loops before lengthening the delay time, and the *

Re: [GENERAL] PostgreSQL with ZFS on Linux

Hi, If you really want ZFS, I would highly recommend looking into FreeBSD (Postgresql works great on it) or if you want to stick with Linux, look into mdadm with LVM or some other filesystem solution. If you want to use ZFS because of its features, take a look at btrfs. It provides a lot of

[GENERAL] Creating an index alters the results returned

Hi, Running postgres 9.1.11 + postgresql-9.3-1100.jdbc41.jar, I experience a strange phenomenon using indexes. I have the following schema: CREATE TABLE wplog (id serial NOT NULL,ts timestamp without time zone, sensor1 real, sensor2 real, noiselevel smallint, CONSTRAINT wplog_pkey PRIMARY KEY

[GENERAL] Why is wal_writer_delay limited to 10s?

Hi, Just to be curious, why is wal_writer_delay limited to 10s? I am using postgresql in an embedded environment where every 10s sensor values are logged and even with synchronous_commit = off and wal_writer_delay=1 this burns quite a lot of nand cycles. For me it wouldn't hurt loosing

Re: [GENERAL] BEFORE UPDATE trigger doesn't change column value

Hi Kevin, Sorry, that's the wrong way around. I should have said: Your BEFORE UPDATE trigger could leave the synced value in NEW alone if force_sync was **true**, and set synced to false otherwise. It could then set NEW.force_sync to false, to leave you ready for the next update. Thanks

[GENERAL] BEFORE UPDATE trigger doesn't change column value

Hi, Sorry for this newbie-question, I am trying for quite some time now to get the following trigger-function to work properly: CREATE OR REPLACE FUNCTION update_synced_column() RETURNS trigger AS $BODY$ BEGIN IF NEW.synced IS NULL THEN NEW.synced := false; END IF; RETURN NEW;

Re: [GENERAL] BEFORE UPDATE trigger doesn't change column value

Hi, NEW reflects what the row will look like after the UPDATE. There is no way to tell which columns were specified in the SET clause of the UPDATE; a column which is omitted from that clause will look exactly the same as a column which is set to the value it already had. Thanks a lot for

[GENERAL] Is there any way to listen to NOTIFY in php without polling?

Hi, Sorry for asking such a newbie-question, I've used a search engine - however I haven't found what I am searching for. Is there any way to listen to NOTIFY in php without polling using a callback or blocking call? I've only found pg_get_notify(), however it requires polling as far as I

Re: [GENERAL] Is there any way to listen to NOTIFY in php without polling?

Hi Bill, Is there any way to listen to NOTIFY in php without polling using a callback or blocking call? Not at this time. Too bad ... Thanks for the confirmation. I'll try to invoke a native libpg binary which stays alive until a NOTIFY is received, should do the trick in case

Re: [GENERAL] Is there any way to listen to NOTIFY in php without polling?

Hi Misa What is the main goal? The main goal is to perform some inter-system communication in the case some rows in one table are updated (very seldom event). even using libpg - you need to call pg notify... Doc says, just using libpgtcl would be possible to get Notify event - without

Re: [GENERAL] Is there any way to listen to NOTIFY in php without polling?

Hi Misa But Example shows - that C app - actually asks all the time to get notify... when gets something more then 4 times - exit... until 4 times loops... The same you can achieve with PHP... As far as I understood, with php I have to query the server again and again, and pg_get_notify will

[GENERAL] Do after update trigger block the current transaction?

Hi, We are currently evaluating the feasibility of executing long-running scripts written in shell-script (plsh) called by triggers (after update/insert) to synchronize two databases. Will triggers (after update specifically) cause the execution of SQL-commands to pause until the trigger-function

Re: [GENERAL] Do after update trigger block the current transaction?

Hi Richard, Will triggers (after update specifically) cause the execution of SQL-commands to pause until the trigger-function has returned (at statement execution time or commit)? The trigger will block. If it didn't then it couldn't abort the transaction if it needed to. Thanks for the

Re: [GENERAL] Do after update trigger block the current transaction?

Hi Richard, Might be worth looking at PgQ - a queueing system underlying Londiste. That would handle tracking the changes in PostgreSQL leaving you to just handle the MySQL end. Timestamps will do the job as long as you are careful to allow enough slack to deal with clock updates. Thanks a

Re: [GENERAL] Question about load balance

Hi, I think I'm trying to learn information what is the technique for managing large databases not to philosophize what was my server. In this case it starts to get very specific about what you are trying to accomplish. Transactional databases offer a lot guarantees, which makes it hard to

Re: [GENERAL] Reasons for postgres processes beeing killed by SIGNAL 9?

Hi again, We are still constantly getting postgresql processes killed by signal 9 from time to time, without any idea why or how. Syslog seems completly clean. In case a postgresql process would exceed some restricted resources like file descriptors, would the kernel choose to terminate it using

Re: [GENERAL] Reasons for postgres processes beeing killed by SIGNAL 9?

Hi Steve, Out of memory or OOM killer?? Any such messages in system logs? That was my first thought too - but I could't find anything indicating an OOM event in the logs. Usually the server only uses ~110mb out of the available 2GB assigned to it. So if this isn't a known postgres behaviour, I

[GENERAL] Reasons for postgres processes beeing killed by SIGNAL 9?

Hi, Recently single postgres processes are killed by SIGNAL 9 on our virtual vvmware managed server without any manual interaction - causing lost transactions. Any ideas what could be the reason? Could postmaster the source of the signal? We are running postgreql 8.4.7 on Linux 64-bit. Thank

Re: [GENERAL] Is there a tool for checking database integrity

HI Tomas, No, at least in the current version. The next version (9.2) will support checksums, but it's meant mostly as a protection against failures at the I/O level. It might catch some memory issues, but it certainly won't be 100% protection. Oh, I see - it was bumped to 9.3 and I've

[GENERAL] Is there a tool for checking database integrity

Hi, Is there a tool for checking a postgresql database for integrity, so I would be notified immediatly if something went wrong e.g. with memory and can go back to the last good backup? Such a tool would guarantee me I am not sitting on a ticking time bomb (corrupted database) without even

Re: [GENERAL] Initdb fails on openwrt in creating template1 database

Hi, Do you have more than one device mounted and if so are you sure you are working on the right one? As often, the problem turned out to be a simple one. With OpenWRT, /var is just a symlink to /tmp - which itself is located on a ramdisk. After moving the data-directory to /postgresql,

Re: [GENERAL] Initdb fails on openwrt in creating template1 database

Hi, There are about 800mb free space on /, any idea what could be the problem here? Could you be running out of file system nodes? Don't think so - its a standard ext4 filesystem on a flush drive, nothing special. Also I've added about 128mb of swap space, so that shouldn't be an issue.

[GENERAL] Initdb fails on openwrt in creating template1 database

Hi, I try to run postgresql on my mips based openwrt router. Although openwrt provides precompiled packages, I have troubles initializing postgresql's file system: # creating template1 database in /var/postgresql/data/base/1 ... FATAL: could not write to file pg_xlog/xlogtemp.1791: No space

[GENERAL] BEFORE UPDATE trigger also fires at insert?

Hi, I've defined a small trigger to increment a field each time the row is updated: CREATE TRIGGER inc_trigger BEFORE UPDATE ON Table FOR EACH ROW EXECUTE PROCEDURE inc_function(); Works quite well, however the trigger is also fired if the table itself is modified. When deleting or inserting

Re: [GENERAL] Howto use COPY FROM with the native API?

Hi Pavel, If so, are there some examples how to use COPY FROM STDIN with the native C API? look to source code on \copy implementation in psql http://doxygen.postgresql.org/bin_2psql_2copy_8c.html http://www.postgresql.org/docs/8.1/static/libpq-copy.html Thanks for the pointer, I also

[GENERAL] Howto use COPY FROM with the native API?

Hi, We are working on an university project for network traffic accounting. We use ulogd-2 to log netfilter packets, however it creates for each packet a seperate transaction and also doesn't use prepared statements, which results in horrible performance. What we are looking for is a

Re: [GENERAL] Howto sort the result of UNION (without modifying its type)?

Hi Robert, does this work for you?        select u.id from (your unions) as u order by u.id Unfourtunatly not, it converts my union-results from INTEGER to RECORD. However, it seems to be possible to order the unions directly: result1 UNION result2 ORDER BY u.id Hmm, the query plan looks

[GENERAL] Howto sort the result of UNION (without modifying its type)?

Hi, I have a query where I UNION several select statements which return IDs of type INTEGER. This works well, if the IDs don't need to be sorted: SELECT id FROM table WHERE id IN ((select id FROM table WHERE ...) UNION (SELECT id FROM table_ WHERE )) However I need the result the UNIONs

Re: [GENERAL] [PERFORM] Are Indices automatically generated for primary keys?

Hi Kevin, Sorry; I didn't mean to be harsh. I also overreacted, sorry about that. Indeed the documentation is well done, as is the software itself =) Thanks, Clemens Sometimes people coming from some other products aren't used to that -- I was just trying to point you in the direction of

[GENERAL] Howto only select secific lines from a result?

Hi, I am working on a graphical table display widget which should be able to display huge amounts of data, by lazy-loading database values. It already works well if the primary-key and line-number are equal. However I don't have any idea howto handle the case where the primary-key contains holes,

Re: [GENERAL] Where to configure pg_xlog file-size?

Hello again, Well, if you were using 8.4 you could fool with configure's --with-wal-segsize option.  Since you're not, look into src/include/pg_config_manual.h.  In either case, expect to do a full recompile and initdb after changing it. Good to know - I plan to upgrade to 8.4/9.0 anyway. I

[GENERAL] Where to configure pg_xlog file-size?

Hi, I am using postgres-8.3 on an embedded ARM9 system. Works pretty well, except for stoarge consumptions. The actual table data is rather small, but postgres creates 2 16mb files in pg_xlog: r...@mesrv:/var/lib/postgresql/8.3/main# ls -la pg_xlog/ total 32820 -rw--- 1 postgres postgres

[GENERAL] looking for a powerful frontend/teport generator

Hi, Sorry for beeing a bit off-topic. Recently I've done some database-fontends, which I used java+swingset+netbeans-gui-builder for. Compared to plain java development is quite fast - however I wonder wether you could recommend db-fontend generators like the infamous access. What I've found so

[GENERAL] Implicit sequence with start value?

Hello, Is it possible to use an implicit sequence with a start value? Something like: CREATE TABLE foo (key SERIAL START 1000 PRIMARY KEY NOT NULL); Thank you in advance, Clemens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Implicit sequence with start value?

Hi Tom, regression=# alter sequence foo_bar_seq start with 1000; ALTER SEQUENCE Completly forgot about that possibility, thanks a lot :) What still puzzles me is how to get the sequence value applied. MySQL's auto_increment simply ignores whatever value is supplied to it, however postgres

Re: [GENERAL] Implicit sequence with start value?

The problem is that I don't have control over the SQL generated, that happens behind the scenes of the JDBCRowSet/SwingSet toolkt/api. Fourtunatly it seems to work when I simply don't bind the textfield :) However now I am facing other, jdbc-driver related problems, however I'll ask on the