[GENERAL] undo update

gresql 8.4 with default config (autovacuum is commented) -- __ Yours sincerely, Ivan Kuznetsov aka Kuzma mailto: kuzma...@gmail.com

Re: [GENERAL] undo update

58, prev:0/5770EEC8] > insert_leaf: s/d/r:1663/90693/107104 tid 18/232 > [cur:0/5770EF40, xid:355075, rmid:11(Btree), len:50/78, prev:0/5770EF04] > insert_leaf: s/d/r:1663/90693/107105 tid 56/107 > [cur:0/5770EF90, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770EF40] > inser

[GENERAL] Re[2]: [GENERAL] PG 9.4.4 issue on French Windows 32 bits

http://postgrespro.ru/windows-en.html Regards, Ivan Panchenko >Среда, 8 июля 2015, 9:54 -07:00 от Adrian Klaver : > >On 07/08/2015 08:40 AM, Thierry Hauchard wrote: >> There is NO problem on 64 bits machines with PG 64 bits. >> (We have not try PG 32 bits on 64 bits machine.) >

[GENERAL] pg_tune replacement

ource code is published on GitHub [2]. What is the difference to pg_tune ? Is it really producing good results for latest PostgreSQL versions ? Thank you very much. 1 - http://pgtune.leopard.in.ua/ 2 - https://github.com/le0pard/pgtune -- Ivan Minčík ivan.min...@gmail.com GPG: 0x79529A1E

[GENERAL] Update statement results in Out of memory

L 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 5.3.1 20160406 (Red Hat 5.3.1-6), 64-bit - POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.0.2, released 2016/01/26" LIBXML="2.9.3" LIBJSON="0.12" RASTER Many thanks, -- Ivan

Re: [GENERAL] Update statement results in Out of memory

e of error. Best regards, Ivan 2016-07-06 15:42 GMT+02:00 Rémi Cura : > You could check the max number of points in your geometries : > > SELECT max(ST_NumPoints(geom)) > FROM ... > > Of course you could still have invalid / abberant geometry, > which you could also check (ST

[GENERAL] Catalog bloat (again)

Hi, I've done my Googling, and it looks like this is a fairly common problem. In my case, there's a collection of hundreds of databases (10 GB+) with apps which are pretty much designed (a long time ago) with heavy use of temp tables - so a non-trivial system. The databases are vacuumed (not-full

Re: [GENERAL] Catalog bloat (again)

On 28 January 2016 at 00:13, Bill Moran wrote: > On Wed, 27 Jan 2016 23:54:37 +0100 > Ivan Voras wrote: > > > So, question #1: WTF? How could this happen, on a regularly vacuumed > > system? Shouldn't the space be reused, at least after a VACUUM? The issue > > he

Re: [GENERAL] Catalog bloat (again)

319 rows spread around in 52856 pages? 2. What are "unused item pointers"? (I agree with your previous suggestions, will see if they can be implemented). On 28 January 2016 at 00:13, Bill Moran wrote: > On Wed, 27 Jan 2016 23:54:37 +0100 > Ivan Voras wrote: > > >

[GENERAL] Streaming replication and slave-local temp tables

Hello, Is it possible (or will it be possible) to issue CREATE TEMP TABLE statements on the read-only slave nodes in master-slave streaming replication in recent version of PostgreSQL (9.4+)?

[GENERAL] Enhancement proposal for psql: add a column to "\di+" to show index type

Hi, As it says in the subject: if any developer is interested in doing so, I think it is useful to see the index type in "\di+" output. The new column could be named "using" to reflect the SQL statement. It would contain entries such as "btree", "BRIN", "GIN", etc.

[GENERAL] Auto Vacuum question?

i set the auto vacuum option to enable. but my database size(hard-disk space) still increased from 420MB to 440MB in 8 hours. most of the operations in this database are the "Select" query command, just few of "Update or Insert". why it can be increased so strongly? after i executed the command " v

Re: [GENERAL] Modelling tags

xtend the basic idea to get the features needed. Regards, Ivan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] import content of XLS file into PostgreSQL

Hello, One more way to do it with mouse clicking only is OpenOffice. Get OO and install PostgreSQL driver into OpenOffice Database application, then you'll be able to import/export spreadsheets to and from database tables and work with DB tables just like they are spreadsheets. Regards,

Re: [GENERAL] import content of XLS file into PostgreSQL

Hello, On 8/8/07, Roberto Mello <[EMAIL PROTECTED]> wrote: > On 8/8/07, Ivan Zolotukhin <[EMAIL PROTECTED]> wrote: > > Hello, > > > > One more way to do it with mouse clicking only is OpenOffice. Get OO > > and install PostgreSQL driver into OpenOffice D

[GENERAL] Best practice for: ERROR: invalid byte sequence for encoding "UTF8"

adding some trash non-UTF-8 characters, database raises an error: invalid byte sequence for encoding "UTF8". What is the best practice to process such a broken strings before passing them to PostgreSQL? Iconv from utf-8 to utf-8 dropping bad characters? -- Rega

Re: [GENERAL] Best practice for: ERROR: invalid byte sequence for encoding "UTF8"

web site user can see some bad error (even if application caught this SQL exception for instance) otherwise. -- Regards, Ivan On 8/15/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > On Wed, Aug 15, 2007 at 03:41:30PM +0400, Ivan Zolotukhin wrote: > > Hello, > &

Re: [GENERAL] Best practice for: ERROR: invalid byte sequence for encoding "UTF8"

encoding_translation = On mbstring.substitute_character = none and broken symbols will be automatically stripped off from the input and output. But I am interested in general solution and better practices anyway... -- Regards, Ivan On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > On 15/08/07,

Re: [GENERAL] How to switch off Snowball stemmer for tsearch2?

configured. > >>> > >>> How to properly switch OFF Snowball stemmer for Russian without turning > >> off > >>> ispell stemmer? (It is really needed, because "Ivanov" is not the same > >> as > >>> "Ivan".) >

Re: [GENERAL] Sthange things happen: SkyTools pgbouncer is NOT a balancer

AFAIK PgBouncer is not a balancer but a connection pooler. Skype said nothing about load balancing in its docs, so they are fair in this sense. Why did you decide it should balance the load? Regards, Ivan On 9/11/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > Hello. > > We disco

[GENERAL] Recovering / undoing transactions?

Hi, About a month or so ago I read a blog entry or an article which seems to have described a method, using dirty hackery with pg_resetxlog and possibly other tools, to forcibly "undo" the database to a previous state. The problem described was that some employee had executed a "DELETE" or "UPDATE

Re: [GENERAL] Recovering / undoing transactions?

On 07/11/2007, Tom Lane <[EMAIL PROTECTED]> wrote: > It's not really possible to do that. The blogger might've thought he'd > accomplished something but I seriously doubt that his database was > consistent afterward. You can go back in time using PITR, if you had > the foresight and resources to

[GENERAL] Simple row serialization?

Hi, I'd like to implement some simple data logging via triggers on a small number of infrequently updated tables and I'm wondering if there are some helpful functions, plugins or idioms that would serialize a row (received for example in a AFTER INSERT trigger) into a string that I'd store in

Re: [GENERAL] Simple row serialization?

Adam Rich wrote: I'd like to implement some simple data logging via triggers on a small number of infrequently updated tables and I'm wondering if there are some helpful functions, plugins or idioms that would serialize a row If you're familiar with perl, you can try PL/Perl. Thanks, but ano

Re: [GENERAL] postgresql scalability issue

On 11/08/10 16:33, umut orhan wrote: > Hi all, > > > I've collected some interesting results during my experiments which I > couldn't > figure out the reason behind them and need your assistance. > > I'm running PostgreSQL 9.0 on a quad-core machine having two level on-chip > cache > hierarc

[GENERAL] Re: Survey on backing up unlogged tables: help us with PostgreSQL development!

On 11/17/10 01:23, Scott Ribe wrote: On Nov 16, 2010, at 3:46 PM, Josh Berkus wrote: ...and will be truncated (emptied) on database restart. I think that's key. Anything that won't survive a database restart, I sure don't expect to survive backup& restore. FWIW, I agree with this reason

[GENERAL] Re: Survey on backing up unlogged tables: help us with PostgreSQL development!

On 11/17/10 02:55, Josh Berkus wrote: If you do wish to have the data tossed out for no good reason every so often, then there ought to be a separate attribute to control that. I'm really having trouble seeing how such behavior would be desirable enough to ever have the server do it for you, o

[GENERAL] Re: Survey on backing up unlogged tables: help us with PostgreSQL development!

On 11/17/10 17:43, A.M. wrote: On Nov 17, 2010, at 11:32 AM, Ivan Voras wrote: On 11/17/10 02:55, Josh Berkus wrote: If you do wish to have the data tossed out for no good reason every so often, then there ought to be a separate attribute to control that. I'm really having trouble s

Re: [GENERAL] Best practice to get performance

On 11/19/10 15:49, Andy Colson wrote: unlogged will only help insert/update performance. Lookup tables sound readonly for a majority of time. (I'm assuming lots of reads and every once and a while updates). I doubt that unlogged tables would speed up lookup tables. Are FreeBSD's temp tables st

Re: [GENERAL] Best practice to get performance

On 11/19/10 23:14, Andy Colson wrote: On 11/19/2010 4:04 PM, Ivan Voras wrote: On 11/19/10 15:49, Andy Colson wrote: unlogged will only help insert/update performance. Lookup tables sound readonly for a majority of time. (I'm assuming lots of reads and every once and a while updates). I

Re: [GENERAL] Bytea error in PostgreSQL 9.0

On 14/12/2010 14:51, tuanhoanganh wrote: Thanks for your help. Is there any .Net or VB tutorial new 9.0 bytea? You do not need to change your code if you add bytea_output = 'escape' # hex, escape into postgresql.conf. -- Sent via pgsql-general mailing list (pgsql-general@po

Re: [GENERAL] Optimal settings for embedded system running PostgreSQL

On 13/01/2011 14:30, Christian Walter wrote: Dear Members, We are currently using PostgreSQL 7.3 on an Embedded System (Based on http://www.msc-ge.com/de/produkte/com/qseven/q7-us15w.html) running Windows XP Embedded / SP3. The onbard flash shows the following performance figures: - Average re

Re: [GENERAL] Optimal settings for embedded system running PostgreSQL

On 13/01/2011 17:31, Christian Walter wrote: Von: pgsql-general-ow...@postgresql.org im Auftrag von Ivan Voras - Average read = 15,6Mb/s - 4Kbyte reads = 3,5Mb/s - 1Kbyte read = 1Mb/s This is very slow. Have you considered something more light-weight like SQLite? This is comparable to a

[GENERAL] Copying databases with extensions - pg_dump question

A fairly frequent operation I do is copying a database between servers, for which I use pg_dump. Since the database contains some extensions - most notably hstore and tsearch2, which need superuser privileges to install, I have a sort of a chicken-and-egg problem: the owner of the database (and

Re: [GENERAL] Copying databases with extensions - pg_dump question

On 21/01/2011 14:39, Bill Moran wrote: In response to Ivan Voras: A fairly frequent operation I do is copying a database between servers, for which I use pg_dump. Since the database contains some extensions - most notably hstore and tsearch2, which need superuser privileges to install, I have

Re: [GENERAL] Copying databases with extensions - pg_dump question

On 21/01/2011 15:55, Bill Moran wrote: On the "no" side, doing this kind of thing is always complex. We have a slew of other, very specialized scripts that do things like convert a production database to a development database by sanitizing sensitive data, or automatically deploy new database o

Re: [GENERAL] multi-tenant vs. multi-cluster

On 18/03/2011 19:17, Ben Chobot wrote: if we're talking an extra 50MB of memory per cluster, that will start to add up. Consider this: each such cluster will have: a) its own database files on the drives (WAL, data - increasing IO) b) its own postgresql processes (many of them) running in mem

[GENERAL] Trigram (pg_trgm) GIN index not used

Hello, I have a table with the following structure: nn=> \d documents Table "public.documents" Column | Type | Modifiers ---+--+ id| integer |

Re: [GENERAL] Trigram (pg_trgm) GIN index not used

On 21/02/2013 12:52, Ivan Voras wrote: > I'd like to use pg_trgm for matching substrings case-insensitively, but > it doesn't seem to use the index: As a sort-of followup, the '%' operator kind of works but takes incredibly long time, and the selectivity estimates

[GENERAL] Getting list of supported types in Postgres

d decimal in results (there is numeric, but I would like to have complete list of supported types, so decimal should be included too). In documentation it is said that typlen of -1 or -2 means that type is variable length, but I don't know how to find out if type can have additional precision?

Re: [GENERAL] Getting list of supported types in Postgres

cific views and tables) - if you can point me to some source file that would be acceptable too :-) Regards, Ivan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting list of supported types in Postgres

On 08/15/13 16:30, Adrian Klaver napisa: On 08/15/2013 02:33 AM, Ivan Radovanovic wrote: On 08/15/13 05:23, Michael Paquier napisa: On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjian wrote: Try psql -E, and run the \dT command to see the query it uses. You have also the following commands: - ¥dT

Re: [GENERAL] Getting list of supported types in Postgres

On 08/15/13 16:49, Adrian Klaver napisa: On 08/15/2013 07:37 AM, Ivan Radovanovic wrote: On 08/15/13 16:30, Adrian Klaver napisa: On 08/15/2013 02:33 AM, Ivan Radovanovic wrote: On 08/15/13 05:23, Michael Paquier napisa: On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjian wrote: Try psql -E, and

Re: [GENERAL] Getting list of supported types in Postgres

On 08/15/13 16:59, Adrian Klaver napisa: On 08/15/2013 07:53 AM, Ivan Radovanovic wrote: Now I just need to find out which types can be indexed (and which types can be part of PK) http://www.postgresql.org/docs/9.2/interactive/indexes.html doesn't list which types can be indexe

Re: [GENERAL] Getting list of supported types in Postgres

On 08/15/13 17:15, Tom Lane napisa: Ivan Radovanovic writes: Thanks Adrian, but question was how to decide which types are indexable A little bit of research in the system-catalogs documentation will show you how to find the types that can be accepted by some index opclass (hint

Re: [GENERAL] Getting list of supported types in Postgres

On 08/15/13 17:27, Adrian Klaver napisa: On 08/15/2013 08:07 AM, Ivan Radovanovic wrote: On 08/15/13 16:59, Adrian Klaver napisa: On 08/15/2013 07:53 AM, Ivan Radovanovic wrote: Now I just need to find out which types can be indexed (and which types can be part of PK) http

[GENERAL] Bug in psql (\dd query)

At least last tuple is what comment statement is inserting into pg_description table Regards, Ivan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Bug in psql (\dd query)

On 08/21/13 16:03, Tom Lane napisa: Ivan Radovanovic writes: I was checking for way to get object comments, and it seems that \dd has bug when it comes to extracting descriptions for constraints. That code looks right to me, and it works according to a simple test: d1=# create table foo (f1

Re: [GENERAL] Bug in psql (\dd query)

On 08/21/13 16:34, Ivan Radovanovic napisa: On 08/21/13 16:03, Tom Lane napisa: Ivan Radovanovic writes: I was checking for way to get object comments, and it seems that \dd has bug when it comes to extracting descriptions for constraints. That code looks right to me, and it works according

[GENERAL] Unique constraint and unique index

ut not to pg_constraint table (although in fact that index is behaving like constraint on table) Is that correct? Regards, Ivan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Foreign key references a unique index instead of a primary key

Hello, I've inherited a situation where: - a table has both a primary key and a unique index on the same field. - at some time, a foreign key was added which references this table (actually, I'm not sure about the sequence of events), which has ended up referencing the unique index in

[GENERAL] Making a unique constraint deferrable?

Hello, If I'm interpreting the manual correctly, this should work: ivoras=# create table foo(a integer, b integer, unique(a,b)); CREATE TABLE ivoras=# \d foo Table "public.foo" Column | Type | Modifiers +-+--- a | integer | b | integer | Indexes:

Re: [GENERAL] Making a unique constraint deferrable?

On 28 February 2017 at 18:03, David G. Johnston wrote: > On Tue, Feb 28, 2017 at 9:50 AM, Ivan Voras wrote: > >> >> ivoras=# alter table foo alter constraint "foo_a_b_key" deferrable; >> ERROR: constraint "foo_a_b_key" of relation "foo" is

[GENERAL] DROP INDEX CASCADE doesn't want to drop unique constraints?

Hello, On trying to drop an index named "employer_employerid_key" which supports a unique constraint: "employer_employerid_key" UNIQUE CONSTRAINT, btree (employerid) I get this error: ERROR: cannot drop index employer_employerid_key because constraint employer_employerid_key on table emplo

[GENERAL] Writing on replicas?

Hello, I have a possibly unusual case, I've asked about it before on this list. There is a bunch of reporting being done regularly on some large databases, which interfere with daily operations performance-wise. So one option is to have hot async replication in place to a secondary server where t

[GENERAL] Log storage

Hello everyone, An inaugural poster here, sorry if I misidentified a list for my question. I am planning to use PostgreSQL as a storage for application logs (lines of text) with the following properties: - Ingest logs at high rate: 3K lines per second minimum, but the more the better as it w

Re: [GENERAL] Log storage

Can't get to hard data right now, but those are app logs that try to be no more than ~100 bytes characters long for readability, and also HTTP logs with long-ish request lines which might put it in the neighborhood of 2K characters. On 10/18/2017 02:30 AM, legrand legrand wrote: What is the

[GENERAL] Roles inherited from a role which is the owner of a database can drop it?

Hello, I just want to verify that what I'm observing is true, and if it is, I'd like to know how to avoid it: 1. There are databases owned by a certain role which is a superuser 2. Nobody logs in with the superuser role unless necessary 3. But they do log in with "developer" roles which are inher

Re: [GENERAL] Roles inherited from a role which is the owner of a database can drop it?

Hello, On 30 October 2017 at 22:10, David G. Johnston wrote: > On Mon, Oct 30, 2017 at 12:25 PM, Ivan Voras wrote: > >> >> 3. But they do log in with "developer" roles which are inherited from the >> owner role. >> >> ​[...]​ > >> I

[GENERAL] debug nonstandard use of \\ in a string literal

s them? Is it possible to have a look what exact queries produced them? -- Regards, Ivan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column'

Re: [GENERAL] debug nonstandard use of \\ in a string literal

Thanks guys, this simple solution worked. Why didn't I guess before?.. On Thu, Feb 28, 2008 at 2:28 PM, Albe Laurenz <[EMAIL PROTECTED]> wrote: > > Ivan Zolotukhin wrote: > > From time to time I face with these well-known warnings in the > > PostgreSQL log, i.e.

Re: [GENERAL] Storing and accessing GeoData( Latitude and Longitude ) in PostgreSQL 8.3

Hello, You may want to have a look also at PgSphere (http://pgfoundry.org/projects/pgsphere) and Q3C (http://q3c.sf.net) which is actually a spherical indexing solution built specially for PostgreSQL with the best performance among all other methods (even within other databases). Regards, Ivan

Re: [GENERAL] tsearch2 on-demand dictionary loading & using functions in tsearch2

n shared memory would help here. We alter dictionaries once per couple of months and would endure even postgres restart after such a change. -- Regards, Ivan >> This is probably a stupid question, but: with PostgreSQL's use of >> shared memory, is it possible to load dictionaries i

Re: [GENERAL] PL/R download

Still does not work for me, HTTP timeout On Tue, May 27, 2008 at 7:35 PM, Joe Conway <[EMAIL PROTECTED]> wrote: > Cindy Makarowsky wrote: >> >> Is the PL/R download still available? The link is dead from the Postgres >> site and also at www.joeconway.com . Is there >> a

Re: [GENERAL] Cannot drop user (PostgreSQL 8.1.11)

I recall I came across similar issue on older (8.1 or 8.2) versions of PostgreSQL some time ago. DB was pretty small so I dump-restored it eventually, but it looks like a bug anyway. I cannot reproduce it at 8.3. -- Regards, Ivan On Mon, Jun 2, 2008 at 7:12 PM, Maxim Boguk <[EMAIL PROTEC

[GENERAL] PREPARE query with IN?

Is it possible to prepare a query with the IN clause in a meaningful way? I could probably do it with a hard-coded number of arguments, like "SELECT x FROM t WHERE y IN ($1, $2, $3)" but that kind of misses the point of using IN for my needs. In any case, it would probably be a good idea to add a

Re: [GENERAL] PREPARE query with IN?

Filip Rembiałkowski wrote: > fi...@filip=# prepare sth(int[]) as select * from ids where id = ANY($1); > PREPARE > > fi...@filip=# execute sth('{1,2,3}'); > id | t > +--- > 1 | eenie > 2 | menie > 3 | moe > (3 rows) Thanks! > 20

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

On 12/09/2013 18:16, Karl Denninger wrote: > > On 9/12/2013 11:11 AM, Patrick Dung wrote: >> While reading some manual of PostgreSQL and MySQL (eg. >> http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html). >> >> I have found that MySQL has stated many incompatibilities and kn

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

On 13 September 2013 21:44, Patrick Dung wrote: > Ivan Voras has replied that the link method work fine in Windows on another > thread. That would be very surprising since I don't run Windows servers :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

[GENERAL] "Recheck conditions" on indexes

Hi, I'm just wondering: in the execution plan such as this one, is the "Recheck Cond" phase what it apparently looks like: an additional check on the data returned by indexes, and why is it necessary? I would have though that indexes are accurate enough? cms=> explain analyze select * from users

Re: [GENERAL] "Recheck conditions" on indexes

On 25/10/2013 11:06, Albe Laurenz wrote: > Just because there is an entry in the index does not imply that the > corresponding table entry is visible for this transaction. > To ascertain that, the table row itself has to be checked. Understood. > PostgreSQL 9.2 introduced "index only scan" which

Re: [GENERAL] Consistent file-level backup of pg data directory

On 08/01/2014 16:09, gator...@yahoo.de wrote: > For machines running database systems, this means, this means, > that I need some way to get a consistent state of some point in > time. It does not particularly matter, which time exactly (in > Unfortunately, it does not look like there is any dire

Re: [GENERAL] how is text-equality handled in postgresql?

On 15/01/2014 10:10, Gábor Farkas wrote: > hi, > > when i create an unique-constraint on a varchar field, how exactly > does postgresql compare the texts? i'm asking because in UNICODE there > are a lot of complexities about this.. > > or in other words, when are two varchars equal in postgres? w

Re: [GENERAL] how is text-equality handled in postgresql?

On 15/01/2014 12:36, Amit Langote wrote: > On Wed, Jan 15, 2014 at 7:39 PM, Ivan Voras wrote: >> On 15/01/2014 10:10, Gábor Farkas wrote: >>> hi, >>> >>> when i create an unique-constraint on a varchar field, how exactly >>> does postgresql compare t

Re: [GENERAL] how is text-equality handled in postgresql?

On 15/01/2014 13:29, Amit Langote wrote: > On Wed, Jan 15, 2014 at 9:02 PM, Ivan Voras wrote: >> On 15/01/2014 12:36, Amit Langote wrote: >>> * In some locales strcoll() can claim that >>> nonidentical strings are >>> * equa

Re: [GENERAL] CLOB & BLOB limitations in PostgreSQL

On 11/04/2014 16:45, Jack.O'sulli...@tessella.com wrote: > With point two, does this mean that any table with a bytea datatype is > limited to 4 billion rows (which would seem in conflict with the > "unlimited rows" shown by http://www.postgresql.org/about)? If we had > rows where the bytea was a

Re: [GENERAL] encrypting data stored in PostgreSQL

On 09/04/2014 22:40, CS_DBA wrote: > Hi All; > > We have a client with this requirement: > > At rest data must be encrypted with a unique client key > > Any thoughts on how to pull this off for PostgreSQL stored data? Some time ago I did this, mostly as an experiment but IIRC it works decently:

[GENERAL] Re: Seg Fault in backend after beginning to use xpath (PG 9.0, FreeBSD 8.1)

On 03/05/2011 07:12, alan bryan wrote: Our developers started to use some xpath features and upon deployment we now have an issue where PostgreSQL is seg faulting periodically. Any ideas on what to look at next would be much appreciated. FreeBSD 8.1 PostgreSQL 9.0.3 (also tried upgrading to 9.0.

Re: [GENERAL] PostgreSQL benchmarked on XFS vs ZFS vs btrfs vs ext4

On 14/09/2011 09:30, Toby Corkindale wrote: > On 14/09/11 12:56, Andy Colson wrote: >> On 09/13/2011 08:15 PM, Toby Corkindale wrote: >>> Hi, >>> Some months ago, I ran some (probably naive) benchmarks looking at how >>> pgbench performed on an identical system with differing filesystems. >>> (on L

[GENERAL] Bulk processing & deletion

Hello, I have a table with a large number of records (millions), on which the following should be performed: 1. Retrieve a set of records by a SELECT query with a WHERE condition 2. Process these in the application 3. Delete them from the table Now, in the default read-co

Re: [GENERAL] Bulk processing & deletion

On 13/10/2011 14:34, Alban Hertroys wrote: >> Any other ideas? > > CREATE TABLE to_delete ( > job_created timestamp NOT NULL DEFAULT now(), > fk_id int NOT NULL > ); > > -- Mark for deletion > INSERT INTO to_delete (fk_id) SELECT id FROM table WHERE condition = true; > > -- Process in app >

Re: [GENERAL] Bulk processing & deletion

On 13 October 2011 20:08, Steve Crawford wrote: > On 10/13/2011 05:20 AM, Ivan Voras wrote: >> >> Hello, >> >> I have a table with a large number of records (millions), on which the >> following should be performed: >> >>        1. Retrieve a

[GENERAL] Custom integer-like type

Hello, I'm creating a new data in C, and everything is proceeding well, except that the data type should be parsed on input like an integer. Maybe it's best if I explain it with an example: Currently, I can do this: INSERT INTO t(my_data_type) VALUES ('1') but I cannot do this: INSERT INTO t(m

[GENERAL] pg_dump, send/recv

Hello, Actually I have sort of two questions rolled into one: when creating custom data types, there's the option to implement *_send() and *_recv() functions in addition to *_in() and *_out(); does pg_dump use them for binary dumps, and, if not, what uses them? Are they only an optional optimizat

[GENERAL] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

On 01/10/2012 15:36, Moshe Jacobson wrote: > I am working on an audit logging trigger that gets called for every row > inserted, updated or deleted on any table. > For this, I need to store a couple of temporary session variables such as > the ID of the user performing the change, which can be set

Re: [GENERAL] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

On 5 October 2012 04:53, Moshe Jacobson wrote: > On Thu, Oct 4, 2012 at 6:12 AM, Ivan Voras wrote: >> >> On 01/10/2012 15:36, Moshe Jacobson wrote: >> > I am working on an audit logging trigger that gets called for every row >> > inserted, updated or deleted on

Re: [GENERAL] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

On 5 October 2012 15:55, Merlin Moncure wrote: > On Fri, Oct 5, 2012 at 3:09 AM, Ivan Voras wrote: >> I think I can make a fairly educated guess that catching exceptions >> while dealing with session variables should be much, much faster than >> creating any kind of a tab

[GENERAL] PostgreSQL and a clustered file system

Hello, Is anyone running PostgreSQL on a clustered file system on Linux? By "clustered" I actually mean "shared", such that the same storage is mounted by different servers at the same time (of course, only one instance of PostgreSQL on only one server can be running on such a setup, and there are

[GENERAL] Querying information_schema [bug?]

) Desk=> select version(); version - PostgreSQL 9.2.1 on amd64-portbld-freebsd8.3, compiled by cc (GCC) 4.2.1 20070831 patched [FreeBSD], 64-bit (1 row) Regards, Ivan -- Sen

[GENERAL] difference in query performance due to the inclusion of a polygon geometry field

-> Seq Scan on zone_allertamento (cost=0.00..4.34 rows=134 width=196003) (actual time=0.006..0.038 rows=134 loops=1)" "Total runtime: 3.355 ms" __ It seems that the difference is primary in the "width" and then in the "cost" of the So

Re: [GENERAL] difference in query performance due to the inclusion of a polygon geometry field

hanks... other suggestions? ivan and mauro -- Ti prego di cercare di non inviarmi files .dwg, .doc, .xls, .ppt. Preferisco formati liberi. Please try to avoid to send me .dwg, .doc, .xls, .ppt files. I prefer free formats. http://it.wikipedia.org/wiki/Formato_aperto http://en.wikipedia.org/wiki/Op

Re: [GENERAL] difference in query performance due to the inclusion of a polygon geometry field

suppose it is not. :-) Thanks Ivan inviato da smartphone Il giorno 30/nov/2012 15:16, "Albe Laurenz" ha scritto: > ivan marchesini wrote: > > Concerning the problem of the same "running time" I'm really surprised > > but I can confirm that the EX

Re: [GENERAL] difference in query performance due to the inclusion of a polygon geometry field

lve this problem? Should I ask to the PostGIS user mailing list? Best regards, Ivan -- Ti prego di cercare di non inviarmi files .dwg, .doc, .xls, .ppt. Preferisco formati liberi. Please try to avoid to send me .dwg, .doc, .xls, .ppt files. I prefer free formats. http://it.wikipedia.

[GENERAL] server registration problems

Hi, I installed postgresql 8.4 and I set port number in installation as 5432. I also installed PostGIS and everything was working well. But now I wanted to create new server with port number 54321 but I can't do it because it gives me error ''Server doesn't listen''. I was reading about that in the

Re: [GENERAL] File compression in WinXP

On 2.5.2010 16:48, pasman pasmański wrote: > Hello. > I'm install postgresql 8.4.3 on WinXPsp3. > Because of small disk i create tablespace > pointing to commpressed folder and move > some tables to it. > Compression is good: 10GB to 3-4GB > speed acceptable (small activity,10 users) > > But is

[GENERAL] hstore problem with UNION?

I've encountered the following problem: ivoras=# create table htest2(id integer, t hstore); CREATE TABLE ivoras=# create table htest3(id integer, t2 hstore); CREATE TABLE ivoras=# select id, t from htest2 union select id,t2 as t from htest3; ERROR: could not identify an ordering operator for t

Re: [GENERAL] hstore problem with UNION?

On 05/10/10 14:10, Jayadevan M wrote: When we do a union, the database has to get rid of duplicates and get distinct values. To achieve this, probably it does a sort. Just guesswork You are right, it looks like I have inverted the logic of UNION and UNION ALL - I actually needed "UNION ALL

Re: [GENERAL] Authentication method for web app

On 05/13/10 09:21, Leonardo F wrote: > Hi all, > > > we're going to deploy a web app that manages users/roles for another > application. > > We want the database to be "safe" from changes made by malicious > users. > > I guess our options are: > > 1) have the db listen only on local connection

Re: [GENERAL] Authentication method for web app

On 14 May 2010 09:08, Leonardo F wrote: >> Personally I would lean toward making >> the bulk of security within the >> application so to simplify everything - the >> database would do what it >> does best - store and manipulate data - and the >> application would be the >> single point of entry. P

[GENERAL] Full text search on a complex schema - a classic problem?

Hello, I have a schema which tracks various pieces of information which would need to be globally searchable. One approach I came up with to make all of the data searchable is to create a view made of UNION ALL queries that would integrate different tables into a common structure which could be un

  1   2   3   4   5   6   7   8   >