[GENERAL] Bytea/Text blob deletion is very slow...

Hi! PG8,4, Window XP, Python. I have a program that makes many picture version from an original with XNView effects. Because I want preserve the integrity, previous datas, I used transactions, and PGSQL. The main problem with blobs that insertion is good, the select is good, but the deletion is

Re: [GENERAL] Transaction with in function

How can we achieve nested transactions? ( may be using save points )with in functions. Is there any work around? -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of A. Kretschmer Sent: Wednesday, May 26, 2010 10:44 AM To:

[GENERAL] Commit every N rows in PL/pgsql

Hi, I need to populate a new column in a Postgres 8.3 table. The SQL would be something like "update t set col_c = col_a + col_b". Unfortunately, this table has 110 million rows, so running that query runs out of memory. In Oracle, I'd turn auto-commit off and write a pl/sql procedure that keeps a

Re: [GENERAL] Transaction with in function

In response to Ravi Katkar : > > > I looking for solution for commit, rollback with in function. You can't use transactions within functions, use savepoints instead. http://www.postgresql.org/docs/current/static/sql-savepoint.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035

[GENERAL] Transaction with in function

I looking for solution for commit, rollback with in function. Below is Example function CREATE OR REPLACE FUNCTION test() RETURNS void AS $BODY$ BEGIN --- setof statements Commit; -- set of statement commit; -- if things goes wrong rollback RETURN; END; $BODY$ LANGUAGE 'plpgsql';

[GENERAL] Fwd: PDXPUG Day at OSCON 2010

It was recommended to me to forward this to -general. Regards, Mark -- Forwarded message -- From: Mark Wong Date: Tue, May 18, 2010 at 6:57 AM Subject: PDXPUG Day at OSCON 2010 To: pgsql-annou...@postgresql.org Thanks to the generosity of O'Reilly, we will be having a full day

Re: [GENERAL] Mysterious empty database name?

Thanks Tom, Alvaro. It was indeed caused by the carriage return. I've renamed the weird db and the problem is resolved. On Wed, May 26, 2010 at 7:14 AM, Tom Lane wrote: > Azlin Rahim writes: >> I tried to do another SELECT on pg_database with the OID and here's >> what I get. Seems like there is

Re: [GENERAL] Mysterious empty database name?

Azlin Rahim writes: > I tried to do another SELECT on pg_database with the OID and here's > what I get. Seems like there is no OID for the weird database. I'm > stumped. You didn't absorb the point about funny formatting. This makes it look even more probable that the database name contains a ca

Re: [GENERAL] Hiding data in postgresql

On Mon, May 24, 2010 at 2:16 PM, Hector Beyers wrote: > > Hi guys, > does ANYONE have any tips on hiding data on a database server? This means > that data is stored in places that is not necessarily picked up in the > schema of the database. I am doing some research on databases and need some > di

Re: [GENERAL] Mysterious empty database name?

I tried to do another SELECT on pg_database with the OID and here's what I get. Seems like there is no OID for the weird database. I'm stumped. Btw, our Postgresql version is 8.1.11. # select oid,* from pg_database; oid | datname | datdba | encoding | datistemplate | datallo

Re: [GENERAL] How to fetch values at regular hours?

Goran Hasse wrote: > Yes timestamp and count - is not good names for columns... > I tried something like; > select name,date_trunc('hour',timestamp),timestamp,count from > counter_log_view where name='CNT-3' and timestamp < '2010-05-23 20:00:00' > order by timestamp limit 10; > name | dat

Re: [GENERAL] Mysterious empty database name?

Excerpts from Azlin Rahim's message of mar may 25 18:23:13 -0400 2010: > In our database list, there is one 'mysterious' database with a blank name. > We don't know how it got there. Maybe somebody SQL-injected you and created it. > Below is the output of pg_database. Does anyone have any idea wh

Re: [GENERAL] Mysterious empty database name?

Azlin Rahim writes: > In our database list, there is one 'mysterious' database with a blank name. > We don't know how it got there. Your mail client has done you no favors as far as preserving the formatting of the SELECT output, but it looks to me like the name of the weird database is probably

[GENERAL] Mysterious empty database name?

In our database list, there is one 'mysterious' database with a blank name. We don't know how it got there. Below is the output of pg_database. Does anyone have any idea why this is happening? Any way to remove the 'mysterious' database safely. datname | datdba | encoding | dat

Re: [GENERAL] How to fetch values at regular hours?

Yes timestamp and count - is not good names for columns... I tried something like; select name,date_trunc('hour',timestamp),timestamp,count from counter_log_view where name='CNT-3' and timestamp < '2010-05-23 20:00:00' order by timestamp limit 10; name | date_trunc | timestamp

Re: [GENERAL] export data to excel

akp geek wrote: On Tue, May 25, 2010 at 2:33 PM, John R Pierce > wrote: akp geek wrote: Deal All - I would like to know if there is any plug in available to export the result to excel from pgadmin. if I do that now,

Re: [GENERAL] How to fetch values at regular hours?

Goran Hasse wrote: > [...] > I would like to do; > freescada=> select * from counter_log_view where name='CNT-3' and timestamp > < '2010-05-23 18:00:00' order by timestamp desc limit 1; > name | timestamp | count > ---+---+--- > CNT-3 | 2010-05-

Re: [GENERAL] export data to excel

I tried this option, the error that I was getting can not use "crypt". The I have used the open office as Peter mentioned and that worked for me. I will try the other options mentioned also thanks for the support Regards On Tue, May 25, 2010 at 2:33 PM, John R Pierce wrote: > akp geek wrote:

Re: [GENERAL] How to fetch values at regular hours?

Goran Hasse wrote: Hello all! I am logging my electricity meeter every five minute! But the problem is that I would like to get values *just before* every hour - like select * from counter_log_view where name='CNT-3' and timestamp < '2010-05-23 22:00:00' order by timestamp desc limit 1; n

[GENERAL] How to fetch values at regular hours?

Hello all! I am logging my electricity meeter every five minute! But the problem is that I would like to get values *just before* every hour - like select * from counter_log_view where name='CNT-3' and timestamp < '2010-05-23 22:00:00' order by timestamp desc limit 1; name | timestamp

Re: [GENERAL] export data to excel

There is also the Java POI classes (Poor Obfuscation Implementation) for importing and exporting doc and docx, HSSF (Horrible SpreadSheet Format) for exporting and importing xls and xlsx, http://poi.apache.org/ There are also implementations for Outlook, Powerpoint and Visio. Java can be integrat

Re: [GENERAL] Hiding data in postgresql

On Tue, May 25, 2010 at 11:30 AM, Hector Beyers wrote: > Maybe to save populate a table with 1000 rows, but the meta-data only knows > of about 500 of them? Only on an export of a dump can you find the data > again. Well, here's your flaw: If you expect the data dumper to be able to export the ro

Re: [GENERAL] Hiding data in postgresql

On 5/25/2010 2:58 AM, Hector Beyers wrote: > > No, I have not considered encrypting or decrypting data. The reason > for this is that I am trying to /secure a database/ by thinking like a > /malicious user / criminal/. I want to hide (for example) fraudulent > data on a database where it is not

Re: [GENERAL] export data to excel

csv can easily be swallowed by Excel as a data source. I like the ODBC based query suggestion better. I've configured Excel to accept user inputs via text input widgets and pass them onto the query as params in the query predicate. I found the "QueryEditor.xla" add-in very valuable in this re

Re: [GENERAL] export data to excel

> Deal All - >                I would like to know if there is any plug in available to > export the result to excel from pgadmin. if I do that now, all the data gets > exported as csv . Appreciate your help > Regards Why not just save to the CSV file to XLS from within Excel? XLS, and to a le

Re: [GENERAL] export data to excel

akp geek wrote: Deal All - I would like to know if there is any plug in available to export the result to excel from pgadmin. if I do that now, all the data gets exported as csv . Appreciate your help configure excel to use the postgres database as an ODBC 'data source' and q

[GENERAL] export data to excel

Deal All - I would like to know if there is any plug in available to export the result to excel from pgadmin. if I do that now, all the data gets exported as csv . Appreciate your help Regards

Re: [GENERAL] Hiding data in postgresql

On 5/24/2010 3:18 PM, Hector Beyers wrote: > Yes, I mean hide. I am approaching the problem out of the perspective > of a malicious user / hacker. > > **snip*** First hiding data is not a solution to secure or block access to information. This only slows people down it does not stop them, neve

[GENERAL] cursor_to_xml iteration of a table

Hi all - I'm currently wrestling with generating XML output from a table that has grown to a size where simply using table_to_xml or query_to_xml is no longer feasible due to the whole result set getting loaded into memory. I've been getting familiar with cursors and the cursor_to_xml command a

Re: [GENERAL] can't install postgres 8.4 on windows 2003 server

Sachin, I was using the windows user the postgres installer created to register the service. That failed. I thought I will try a admin user and I was able to register the service. Then I ran all the failed vb scripts in the installer which was listed in the install-postgresql.log file: startupcfg

[GENERAL] Statement Pooling

Hi, we are running java6/hibernate/c3p0/postgresql stack. Our JDBC Driver is 8.4-701.jdbc3 I have a few questions about Prepared Statements. I have read At the moment we have c3p0.m

Re: [GENERAL] Hiding data in postgresql

Hector Beyers wrote: > thank you for your replies yesterday on this topic. I have one more question > though: > Does someone have any ideas how I can hide data without the meta data > noticing? To explain further, I would like to save some collection of data > where the meta-data does not see it

Re: [GENERAL] Hiding data in postgresql

Hi guys, thank you for your replies yesterday on this topic. I have one more question though: Does someone have any ideas how I can hide data without the meta data noticing? To explain further, I would like to save some collection of data where the meta-data does not see it. I am trying to do som

Re: [GENERAL] ALTER Bigserial error

Scott, Sorry about the late response. Curiously, what does the SQL spec have to say about autoincrement and defaults and such? >From >http://savage.net.au/SQL/sql-2003-2.bnf.html#identity%20column%20specification ... Define a column of a base table. ::= [ | ] [ ] [

[OT] Re: [GENERAL] update from postgresql 8.3.7 to 8.4.4

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > At least there's a plain text fallback. Messages in HTML only > are totally unreadable in a plaintext MUA, and should IMO be > bounced from the list. Not totally unreadable. Good MUAs find ways to handle it. For example, my mutt[1] is more

Re: [GENERAL] pg_dump cannot connect when executing by a script

Luca Ferrari writes: > for my case: when I executed pg_dump against a database from a script (that > will be executed by cron) I got the following error: > pg_dump: [archiver (db)] connection to database "webcalendardb" failed: could > not connect to server: No such file or directory >

Re: [GENERAL] can't install postgres 8.4 on windows 2003 server

Herald, I was lazy to type the whole documents and settings folder. I thought it was obvious:) Massa, Harald Armin wrote: > > Ritas, > >>modules. Please check the installation logs in C:/docs&setttings/user/local >>settings/temp for details". The log file doesn't have any error, but it > > t

Re: [GENERAL] can't install postgres 8.4 on windows 2003 server

I tried that and I get the same error: pg_ctl: could not register service "postgresql-8.4": error code 0 Sachin Srivastava-2 wrote: > > Hi, > > What happens if you register the service with pg_ctl directly as: > > \bin\pg_ctl.exe register -N -U > -P -D -w > > > On 5/24/10 11:58 PM,

Re: [GENERAL] pg_dump cannot connect when executing by a script

On Tuesday 25 May 2010 01:13:40 pm A. Kretschmer's cat walking on the keyboard wrote: > Your unix-scket isn't in /tmp. > > Start psql -h localhost and type: > > show unix_socket_directory; > > This will show you the corrent path to the unix-socket. You can use that > for pg_dump with option -h

Re: [GENERAL] can't install postgres 8.4 on windows 2003 server

Ritas, >modules. Please check the installation logs in C:/docs&setttings/user/local >settings/temp for details". The log file doesn't have any error, but it that directory name for "docs and settings" looks strange to me. (disclaimer: using German language versions of Windows); I am used to "docu

Re: [GENERAL] Apache authorization using postgres

I really like the idea of using postgres, as opposed to the file system, to store user names and passwords for http authorization on apache. It is a very good fit and facilitates data collection, etc. On the other hand, there is no dichotomy, because what is stored in a file can also be st

Re: [GENERAL] pg_dump cannot connect when executing by a script

In response to Luca Ferrari : > Hi all, > I've found in the net a lot of problems similar to mine, but not the solution > for my case: when I executed pg_dump against a database from a script (that > will be executed by cron) I got the following error: > > pg_dump: [archiver (db)] connection to

[GENERAL] pg_dump cannot connect when executing by a script

Hi all, I've found in the net a lot of problems similar to mine, but not the solution for my case: when I executed pg_dump against a database from a script (that will be executed by cron) I got the following error: pg_dump: [archiver (db)] connection to database "webcalendardb" failed: could no

Re: [GENERAL] Hiding data in postgresql

On Mon, May 24, 2010 at 05:04:10PM -0400, Merlin Moncure wrote: > Let me humbly state that the #1 problem that beginners face with > security and encryption is focusing too much on the mechanics and not > enough on the 'big picture' issues: One more that OP seems to be avoiding is why would anybod

Re: [GENERAL] uppdate from postgersql 8.3.7 to 8.4.4

On Tuesday 25. May 2010 12.15.14 Alban Hertroys wrote: > I know it's totally unrelated, but when did it become popular to send (HTML) messages in a very small blue font? I find this rather hard to read and usually fall back to the plain text alternative (which is included, thankfully). At least

Re: [GENERAL] uppdate from postgersql 8.3.7 to 8.4.4

On Tue, May 25, 2010 at 11:15 AM, Alban Hertroys wrote: > On 25 May 2010, at 11:38, Malm Paul wrote: > >> Hi, >> I'm trying to update postgresql ver 8.7.3 to 8.4.4 > > I know it's totally unrelated, but when did it become popular to send (HTML) > messages in a very small blue font? I find this ra

Re: [GENERAL] uppdate from postgersql 8.3.7 to 8.4.4

On 25 May 2010, at 11:38, Malm Paul wrote: > Hi, > I'm trying to update postgresql ver 8.7.3 to 8.4.4 I know it's totally unrelated, but when did it become popular to send (HTML) messages in a very small blue font? I find this rather hard to read and usually fall back to the plain text alternat

Re: [GENERAL] can't install postgres 8.4 on windows 2003 server

Hi, What happens if you register the service with pg_ctl directly as: \bin\pg_ctl.exe register -N -U -P -D -w On 5/24/10 11:58 PM, ritas wrote: Thanks Sachin for your response. I have attached the install-postgresql.log. Also, if it helps, I have the following error in my event viewer:

Re: [GENERAL] uppdate from postgersql 8.3.7 to 8.4.4

On 5/25/10 3:08 PM, Malm Paul wrote: Hi, I'm trying to update postgresql ver 8.7.3 to 8.4.4 The One-Click installer will not upgrade 8.3.7 to 8.4.4 rather it will create a parallel 8.4.4 installation as both have different major versions. At the end of the installation when trying to styart the

Re: [GENERAL] Apache authorization using postgres

On 2010-05-24, John Gage wrote: > Is this the latest on this subject? Debian has it it a precompiled binary package. Blastwave is/was a solaris thing AFAICT And I guessing you want it for apple. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subsc

[GENERAL] uppdate from postgersql 8.3.7 to 8.4.4

Hi, I'm trying to update postgresql ver 8.7.3 to 8.4.4 At the end of the installation when trying to styart the database server I get the following error meassage: "Problem running post-install step. Installation may not complete correctly. Failed to start the database server" And when I try

Re: [GENERAL] Hiding data in postgresql

No, I have not considered encrypting or decrypting data. The reason for this is that I am trying to *secure a database* by thinking like a *malicious user / criminal*. I want to hide (for example) fraudulent data on a database where it is not easily seen by others and then build a tool to detect th