Re: [GENERAL] ANALYZE command question

Igor Stassiy wrote: > Will calling ANALYZE table; twice in a row actually run the command twice ? > Or there is some sort of > check that if the table is not changed since the time of first call the > second command will not > actually be run? The statistics will be calculated each time you run

Re: [GENERAL] Weight BLOB objects in postgreSQL? How?

María Dovale wrote: > Thanks, I mean, how heavy it is in kB. You can use lo_lseek64 to find out how big a large object is. To find out the size of large object 24858, you can SELECT lo_lseek64(lo_open(24858, 262144), 0, 2); The last "2" here is SEEK_END from /usr/include/unistd.h, so it may be t

Re: [GENERAL] Update using non-existent fields does not throw an error

Rob Richardson wrote: > An update query is apparently succeeding, even though the query refers to > fields that do not exist. > Here’s the query: > > update inventory set > x_coordinate = (select x_coordinate from bases where base = '101'), > y_coordinate = (select y_coordinate from bases where b

Re: [GENERAL] Reg: PL/pgSQL commit and rollback

Medhavi Mahansaria wrote: > I am writing a porting a procedure running in oracle to a PL/pgSQL function. > > I need to use commit and rollback in my function. > > I have read that usage of commit and rollback is not possible in PL/pgSQL, > however savepoints can be > used. > > even when i use s

Re: [GENERAL] COPY command file name encoding issue (UTF8/WIN1252)

Pujol Mathieu wrote: > I have a problem using COPY command with a file name containing non > ASCII characters. > I use Postgres 9.3.5 x64 on a Windows 7. > OS local encoding is WIN1252. > My database is encoded in UTF8. > I initiate client connection with libpq, connection encoding is set to UTF8.

Re: [GENERAL] schema or database

Michael Cheung wrote: > I have many similar database to store data for every customer. > Structure of database is almost the same. > As I use same application to control all these data, so I can only use > one database user to connect to these database. > And I have no needs to query table for diff

Re: [GENERAL] fillfactor and cluster table vs ZFS copy-on-write

Geoff Speicher wrote: > On Thu, Apr 16, 2015 at 4:56 PM, Qingqing Zhou > wrote: >> On Thu, Apr 16, 2015 at 5:09 AM, Geoff Speicher >> wrote: >>> ZFS implements copy-on-write, so when PostgreSQL modifies a block on disk, >>> the filesystem writes a new block rather than updating the existing blo

Re: [GENERAL] clearing of the transactions shown in pg_locks

Mitu Verma wrote: > I have killed the script, but still the query is showing in pg_stat and > pg_locks. > > Please help me how to clear the pg_locks from the transaction it is already > holding, if there is > something in PostgreSQL which can clear the pg_stat and pg_locks? [...] > fm_db_Serve

Re: [GENERAL] Success story full text search

Frank Langel wrote: > Does someone have a success story of using Postgres Full Search Capability > with significant data, lets say > 50-100 GB ? How about http://www.postgresql.org/search/?m=1 Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To m

Re: [GENERAL] Block Corruption issue..

Sachin Srivastava wrote: > Could you help us solving the below error which we are getting during taking > pg_dump. > > pg_dump: SQL command failed > > pg_dump: Error message from server: ERROR: invalid page header in block > 14521215 of relation > pg_tblspc/18140340/PG_9.1_201105231/18140346/

[GENERAL] Re: moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information

Yuri Budilov wrote: > My employer is evaluating PostgreSQL as a possible replacement for Oracle 11g > R2 and > MS-SQL 2008 R2 for some systems. > I am completely new to PostgreSQL but experienced in MS-SQL and also in > Oracle 11g R2. > We need to establish what PostgreSQL is good at and not so g

Re: [GENERAL] moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information

Maxim Boguk wrote: >> database and transaction log backup compression? not available? > Transaction log backup compression not available (however could be easily > archived via external utilities like bzip2). Well, in PostgreSQL you backup transaction logs by setting "archive_command", which is

Re: [GENERAL] moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information

Scott Marlowe wrote: > On Sat, May 9, 2015 at 11:20 PM, Albe Laurenz wrote: >> Maxim Boguk wrote: >>> It's depend where a corruption happen, if pages become corrupted due to some >>> problems with physical storage (filesystem) in that case a replica data >>

Re: [GENERAL] noobie join question

Steve Clark wrote: > I am having trouble trying to figure out > how to get the result listed at the bottom. That's a bit tough, since you don't describe the desired result. > I have 3 tables units, types of units which has a description of the units, > and a table that list associations of the un

Re: [GENERAL]

Sachin Srivastava wrote: > How can I fast my daily pg_dump backup. Can I use parallel option(Which is > introduced in Postgres 9.3) > with Postgres 9.1. There is any way I can use this is for 9.1 database. You cannot do that. Switch to file system backup, that is much faster. Yours, Laurenz Alb

Re: [GENERAL] Documentation bug?

Thomas Kellerer wrote: > I just noticed that you can do something like this (using 9.4.1): > >select array[1,2,3] - 3 > > which is doing the same thing as: > >select array_remove(array[1,2,3],3) I can't reproduce this on my PostgreSQL 9.4.1: test=> select array[1,2,3] - 3; ERROR: oper

Re: [GENERAL] Memory Utilization Issue

Sachin Srivastava wrote: > Always my server memory utilization is remain >99%. I have 4 DB server and > RAM of the server is (32 > GB, 64 GB, 64 GB and 132 GB). In every server always we are getting the > memory utilization > 99%. > Kindly suggest why this problem is and which parameter will reso

Re: [GENERAL] Optimizing a read-only database

Sameer Thakur wrote: > You could disable fsync as write reliability is not relevant That is bad advice. If there are no writes, fsync won't hurt anyway. Never disable fsync for anything but test systems. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) T

Re: [GENERAL] Can we simulate Oracle Flashback with pg_export_snapshot()?

William Dunn wrote: > Just had an idea and could use some feedback. If we start a transaction, > leave it idle, and use > pg_export_snapshot() to get its snapshot_id MVCC will hold all the tuples as > of that transaction's > start and any other transaction can see the state of the database as of

Re: [GENERAL] Different result depending on order of joins

Nicklas Avén wrote: > I was a little surprised by this behavior. > Is this what is supposed to happen? > > This query returns what I want: > > with > a as (select generate_series(1,3) a_val) > ,b as (select generate_series(1,2) b_val) > ,c as (select generate_series(1,1) c_val) > select * from a

Re: [GENERAL] [tsvector] to_tsvector called multiple times

Sven R. Kunze wrote: > the following stemming results made me curious: > > select to_tsvector('german', 'systeme'); > 'system':1 > select to_tsvector('german', 'systemes'); > 'system':1 > select to_tsvector('german', 'systems'); > 'system':1 > select to_tsvector('german', 'systemen'); > 'system':1

Re: [GENERAL] [tsvector] to_tsvector called multiple times

Sven R. Kunze wrote: > However, are you sure, I am using snowball? Maybe, I am reading the > documenation wrong: test=> SELECT * FROM ts_debug('german', 'system'); alias | description | token | dictionaries | dictionary | lexemes ---+-++---+-

Re: [GENERAL] [tsvector] to_tsvector called multiple times

Sven R. Kunze wrote: > Maybe, I have difficulties to understand the relationship/dependencies > between all these 'maybe' available dictionary/parser/stemmer packages. > > What happens if I install all packages for a single language? (hunspell, > myspell, ispell, snowball) > > Are they complement

Re: [GENERAL] [tsvector] to_tsvector called multiple times

Sven R. Kunze wrote: > I think I understand now. > > Thus, the issue at hand could (maybe) be solved by passing words first > to one of those more elaborate dictionaries (myspell, hunspell or > ispell) and if still necessary then to snowball. > > Did I get this right? I have never experimented w

Re: [GENERAL] Can we simulate Oracle Flashback with pg_export_snapshot()?

William Dunn wrote: > In terms of benefit over a lagging replica Flashback has the benefit of being > transparent to the user > (the user can query over the same database connection, etc), it does not > incur the full cost of having > a replica... Yes, Flashback (in all ist forms) is something t

[GENERAL] Re: duplicate key value violates unique constraint "pg_class_relname_nsp_index"

Mitu Verma wrote: > Following error is continuously seen with the postgreSQL database which we > are using at customer site. > > Current Errors observed: > ./fm_db_VoiceReprocessing1/data/pg_log/postgresql-04.log:2015-04-04 01:00:16 > CESTERROR: duplicate key value violates unique constraint >

Re: [GENERAL] Re: duplicate key value violates unique constraint "pg_class_relname_nsp_index"

> From: Pete Hollobon [mailto:postg...@hollobon.com] > On 27 May 2015 at 09:57, Albe Laurenz wrote: >> Mitu Verma wrote: >>> Following error is continuously seen with the postgreSQL database which we >>> are using at customer site. >>> >>> Current

Re: [GENERAL] date type changing to timestamp without time zone in postgres 9.4

Adrian Klaver wrote: > On 05/30/2015 10:05 PM, Rishi Gokhale wrote: >> When I create a table with a column whose type is date the type gets >> forced to timestamp without timezone after it gets created >> >> ops=# CREATE TABLE test ( >> ops(# namevarchar(40) NOT NULL, >> ops(# start dat

Re: [GENERAL] date type changing to timestamp without time zone in postgres 9.4

Rishi Gokhale wrote: > Thanks very much for your quick responses. I am indeed using EDB's postgres > plus. > > It looks like it has a function thats forcing the date type to change to a > timestamp. I actually > deleted that function, but it still didn't help. You shouldn't delete any functions

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

Andreas Ulbrich wrote: > I'm in a handle for a trigger for TRUNCATE. Is it possible to find out > whether the TRUNCATE TABLE ist called with CASCADE? I don't think there is. But you can find out the table where the trigger is defined and examine if any foreign key constraints are referring to it.

Re: [GENERAL] pg_start_backup does not actually allow for consistent, file-level backup

otheus uibk wrote: > The manual and in this mailing list, the claim is made that consistent, > file-level backups may be made > by bracketing the file-copy operation with the postgresql pg_start_backup and > pg_stop_backup > operations. Many people including myself have found that in some > cir

Re: [GENERAL] pg_start_backup does not actually allow for consistent, file-level backup

otheus uibk wrote: > Just to nit-pick, I see nowhere in either version of the manual the > indication that it is normal for > postgresql to continue to update files in its data catalog between > pg_start_backup and pg_stop_backup. > The closest I see comes in this paragraph: > > ** Some file sys

Re: [GENERAL] 9.3.9 ?

Birta Levente wrote: > In the postgresql yum repo appeared the 9.3.9 and 9.4.4, but on the > postgresql.org nothing about the new version. Where I can see the changelog? AFAIK, it is being packaged and will be announced soon. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-gen

Re: [GENERAL] Compression function

Leonardo M. Ramé wrote: > Hi, does anyone know if there's a compression function to let me store > in gzipped/deflate format TEXT or Bytea fields. > > Please correct me if I'm wrong, but I also wonder if this function is > really needed since I've read large objects are stored with TOAST, hence >

Re: [GENERAL] pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux

Douglas Stetner wrote: > Looking for confirmation there is an issue with pg_dump failing after upgrade > to openssl-1.0.1e- > 30.el6_6.11.x86_64 on redhat linux. > > -bash-4.1$ pg_dump -V > pg_dump (PostgreSQL) 8.4.9 > > -bash-4.1$ pg_dump -h localhost -C Hogwarts -a -t mafs -f zz > pg_dump: Dum

Re: [GENERAL] valgrind

Peter Kroon wrote: > ==3814== Memcheck, a memory error detector > ==3814== Copyright (C) 2002-2013, and GNU GPL'd, by Julian Seward et al. > ==3814== Using Valgrind-3.10.1 and LibVEX; rerun with -h for copyright info > ==3814== Command: ./pgsql_check > ==3814== > ==3814== > ==3814== HEAP SUMMARY: >

Re: [GENERAL] How to craft a query that uses memory?

Holger Friedrich wrote: > So how do I craft a query that actually does use lots of memory? You increase the parameter "work_mem". You can do that globally in postgresql.conf or with SET for one session or with SET LOCAL for one transaction. Yours, Laurenz Albe -- Sent via pgsql-general mailing

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

Adrian Klaver wrote: > On 06/20/2015 12:41 PM, Charles Clavadetscher wrote: >> I just made a short test with the code provided. As Bill mentioned the >> moment when the trigger is fired is essential. >> I made a test with both before (worked) and after (did not work because >> the row was already i

Re: [GENERAL] Postgresql 9.2 has standby server lost data?

Paula Price wrote: > I have Postgresql 9.2.10 streaming replication set up with log shipping in > case the replication falls behind. I discovered that the log-shipping had > been disabled at some point in time. I enabled the log shipping again. > > If at some point in time the streaming replicat

Re: [GENERAL] Re: pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux

Piotr Gackiewicz wrote: > Tom Lane wrote: >> Douglas Stetner writes: >>> Looking for confirmation there is an issue with pg_dump failing after >>> upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux. >> >> Quick thought --- did you restart the Postgres service after upgrading >> openssl?

Re: [GENERAL]

Bruno Hass de Andrade wrote: > Hi. My company have servers that run postgres for storing some logs and > serving django web interfaces > for management of the server itself. In the last days some servers stopped > serving the web interface, > and syslog show this error: > > Jun 23 04:40:19

Re: [GENERAL] Counting the occurences of a substring within a very large text

Marc Mamin wrote: > I'd like to count the number linebreaks within a string, > but I get a memory allocation error when using regexp_matches or > regexp_split_to_table. > > Any idea for an alternative to this problem ? > > select count(*)-1 from > ( select regexp_split_to_table(full_message,'

Re: [GENERAL] Counting the occurences of a substring within a very large text

Marc Mamin wrote: >>> I'd like to count the number linebreaks within a string, but I get a >>> memory allocation error when using regexp_matches or regexp_split_to_table. >> Does any of these two work: [...] > no, they both yeld the same error. > > a new string functions for this would be nice,

Re: [GENERAL] could not fork new process for connection: Resource temporarily unavailable

Jimit Amin wrote: > I have heavy transaction load production database 9.3 PPAS .Today Database is > not able to give new > process. I checked pg_stat_activity , there are so many transaction in > waiting stage because of one > procedure and lock on one table (Code inside procedure) [...] > co

Re: [GENERAL] Creating a user for pg_start_backup

Andrew Beverley wrote: > I'm setting up hot backups on my database server. As such, I'd like to set up > a > Postgres user that has access to only pg_start_backup and pg_stop_backup. > > I'm unable to work out how to do this with the various GRANT options. Can > someone > point me in the right d

Re: [GENERAL] Drop down in connect time between 9.3.6 and 9.3.9 ?

Marc Mamin wrote: > We've just upgraded some productive servers from 9.3.6 to 9.3.9, and it seems > that the time to get a > connection (or possibly to disconnect) has become much slower. > These tests are consistent when run against different servers. > > tests with 9.4.1 show fast times, but we

Re: [GENERAL] postgresql-ctl systemd failed: permission denied

arnaud gaboury wrote: > On Fedora 22 > > % pg_ctl -V > pg_ctl (PostgreSQL) 9.4.4 > > > % systemctl status postgresql.service -l > ● postgresql.service - PostgreSQL database server >Loaded: loaded (/etc/systemd/system/postgresql.

Re: [GENERAL] conn = PQconnectdb(conninfo);

Peter Kroon wrote: > I've found perhaps a bug. > I've narrowed down my code and the problem is indeed at: conn = > PQconnectdb(conninfo); > > My connection string: host=192.168.178.12 dbname=DATABASE user=foo > password=bar > > When I remove key/value host=xxx then everything is OK. Valgrind me

Re: [GENERAL] scaling postgres - can child tables be in a different tablespace?

Chris Withers wrote: > This raises an interesting question: can a child table be in a different > tablespace to its parent and other children of that parent? Yes. Inheritance is a logical concept and is independent of physical placement. Yours, Laurenz Albe -- Sent via pgsql-general mailing li

Re: [GENERAL] conn = PQconnectdb(conninfo);

Peter Kroon wrote: >>> I've found perhaps a bug. >>> I've narrowed down my code and the problem is indeed at: conn = >>> PQconnectdb(conninfo); >>> >>> My connection string: host=192.168.178.12 dbname=DATABASE user=foo >>> password=bar >>> >>> When I remove key/value host=xxx then everything is O

Re: [GENERAL] SELECT clause without parameters

pinker wrote: > I would like to ask what's the reason of change SELECT behaviour. > In distributions below 9.4 SELECT without any parameters caused a syntax > error and now gives empty set. Was it made for some bigger aim ? :) > > for instance 8.4: > postgres=# select version(); >

Re: [GENERAL] How to EXPLAIN a trigger function

Zdenek Belehrádek wrote: > We > would like to know if there is simple way to EXPLAIN the trigger function, so > we could optimize it. I believe that the auto_explain module could help you: http://www.postgresql.org/docs/current/static/auto-explain.html If you turn on "auto_explain.log_nested_stat

Re: [GENERAL] Dangers of mislabelled immutable functions

Jeff Janes wrote: > I want to index the textual representations of a table's rows. > > You can cast a row to text by using the name of the table where you would > usually use the name of a > column, like "table_name::text". But this is not immutable and so can't be > used in an expression > ind

Re: [GENERAL] Postgresql C extension and SIGSEGV

Etienne Champetier wrote: > We are planning to add a C extension > (https://github.com/petropavel13/pg_rrule) to our shared > postgresql cluster, and wondering what are the risk? (looking for the worst > case scenario here) > > If there is a SIGSEGV, SIGBUS, SIGABRT ..., is the whole server stop

Re: [GENERAL] Buffers: shared hit/read to shared_buffers dependence

Pavel Suderevsky wrote: > When I have been passing through "Understanding explain" manual > (http://www.dalibo.org/_media/understanding_explain.pdf) > I've faced some strange situation when table with size of 65MB completely > placed in cache with shared_buffers=320MB and it doesn't with shared_b

Re: [GENERAL] Online backup of PostgreSQL data.

John R Pierce wrote: > to copy the data directory and have it be useful you need to bracket the copy > with calls to > pg_start_backup() and pg_stop_backup() this ensures the data files are > coherent. this is in > fact what pg_basebackup does for you I apologize for my fussiness, but

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

pinker wrote: > I've tried to write audit trigger which fires only when data changed, so I > used "WHEN (OLD.* IS > DISTINCT FROM NEW.*)" clause as described in documentation > . Should > this clause be independent > from data typ

Re: [GENERAL] Delete trigger

Leif Jensen wrote: >If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete > anything. I only want to > delete if I do "DELETE FROM devicegroup WHERE groupid=x AND ctrlid=y AND > userid=z". I don't wanna let > anyone delete more than 1 row at a time. I can't think of a way t

Re: [GENERAL] to pg

Ramesh T wrote: > CREATE UNIQUE INDEX idx_load_pick ON pick (case picked when picked='y' then > load_id else null end ); > > how can i convert case expressed to postgres..above it is oracle. CREATE TABLE pick (picked char(1), load_id integer); CREATE FUNCTION picked_loadid(character, integer)

Re: [GENERAL] Selecting pairs of numbers

Charles Clavadetscher wrote: >> aklaver@test=> create table pr_test(x int, y int); >> >> aklaver@test=> select * from pr_test where (x, y) between (1, 3) and >> (3,2) order by x,y; >> x | y >> ---+--- >> 1 | 3 >> 1 | 4 >> 2 | 1 >> 2 | 2 >> 2 | 3 >> 2 | 4 >> 3 | 1 >> 3 | 2 > > +1

Re: [GENERAL] Version management for extensions

Jeff Janes wrote: > I am facing a scenario where I have different version of an extension, say > 1.0 and 2.0, which have > some different functionality between them (so not merely a bug fix), so > people might want to continue > to use 1.0. > > But changes to the PostgreSQL software between majo

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

Adrian Klaver wrote: > On 10/08/2015 11:32 PM, Victor Blomqvist wrote: >> I have a heavily used PostgreSQL 9.3.5 database on CentOS 6. Sometimes I >> need to add/remove columns, preferably without any service >> interruptions, but I get temporary errors. >> >> I follow the safe operations list from

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

Adrian Klaver wrote: >>> For the reason why this is happening see: >>> >>> http://www.postgresql.org/docs/9.4/interactive/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING >> >> Yes, but the ALTER TABLE causes the plan to be recreated the next time. > > But does it? From the link above: > > "Becau

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

Victor Blomqvist wrote: [race condition causes errors due to stale plans immediately after ALTER TABLE DROP] > Note that these errors most of the time only happens very briefly at the same > time as the ALTER is > run. When I did some experiments today the server in total had around 3k > req/s w

Re: [GENERAL] pgpool ssl handshake failure

AI Rumman wrote: > I am using pgpool-II version 3.4.3 (tataraboshi). > Where my database is Postgresql 8.4. > > I am trying to configure ssl mode from client and between pgpool and database > it is non-ssl. > I configured as document and now I am getting this in my log: > > 2015-10-13 22:1

Re: [GENERAL] interperting type oid in C code

Ken Been wrote: > I'm working on a foreign data wrapper and I want to switch based on the > column type. > Specifically, if the column type in the external table is the same as in the > (locally defined) foreign > table then I can get some speedup for some types. > > Through the ForeignScanState

Re: [GENERAL] pg_archivecleanup not deleting anything?

Paul Jungwirth wrote: > I'm running Postgres 9.3 in a warm standby configuration, and the slave > has this setting in recovery.conf: > > archive_cleanup_command = '/usr/lib/postgresql/9.3/bin/pg_archivecleanup > /secure/pgsql/archive/ %r' > > But I noticed that the archive directory had files goi

Re: [GENERAL] pg_archivecleanup not deleting anything?

Michael Paquier wrote: >> So, as Albe posted pg_archivecleanup is only cleaning up the WAL files, not >> the auxiliary files. The WAL files would be the ones with no extension and a >> size of 16 MB(unless someone changed the compile settings). > > The docs mention that "all WAL files" preceding a

Re: [GENERAL] pg_archivecleanup not deleting anything?

Michael Paquier wrote: >>> The docs mention that "all WAL files" preceding a given point are >>> removed, personally I understand that as "all 16MB-size segments shall >>> die", hence excluding backup and history files from the stack. But one >>> may understand that "WAL files" means everything in

Re: [GENERAL] pg_archivecleanup not deleting anything?

Michael Paquier wrote: >>> Something among those lines? >> >>> + >>> + WAL file segments and WAL file segments with .partial >>> + are deleted, while timeline history files and backup history files are >>> not. >>> + >> >> "WAL file segments with .partial" sounds strange. >> What about "WA

Re: [GENERAL] xa compatibility

Xaver Thum wrote: > is there an option (provided by Postgres) accessing a Postgres DB via the > standard XA interface ? > > I don't mean the usage of JDBC's class PGXADataSource, > but the usual XA methods xa_open, xa_prepare, xa_commit, ... of the XA > standard. I am not sure why there is no

Re: [GENERAL] can postgres run well on NFS mounted partitions?

anj patnaik wrote: > Can anyone advise if there are problems running postgres over NFS mounted > partitions? > > I do need reliability and high speed. I have got the advice not to use NFS from a number of people who should know, but there are also knowledgable people who use PostgreSQL with NFS.

Re: [GENERAL] can postgres run well on NFS mounted partitions?

Scott Mead wrote: > Don't do it. Period. I've used 4 big-vendor appliances with NFS as well as > my own server. With > maybe 3 exceptions, most of the 'total-data-loss' scenarios I've dealt with > regarding transactional > data was due to NFS. Can you share more details? What happened and wh

Re: [GENERAL] attempting to install tds_fw-master on redhat

Mammarelli, Joanne T wrote: > The following postgresql rpm is installed .. > > rpm -qil postgresql-server-9.2.13-1.el7_1.x86_64 > > on redhat 7 > [root@scsblnx-994457 tds_fdw-master]# make USE_PXGS=1 install > > Makefile:53: /usr/lib64/pgsql/pgxs/src/makefiles/pgxs.mk: No such file or > direcy

Re: [GENERAL] can postgres run well on NFS mounted partitions?

John R Pierce wrote: > On 11/11/2015 12:37 AM, Albe Laurenz wrote: > > It would be good to know of other pitfalls; I (and no doubt not only I) > > keep getting asked why we shouldn't run PostgreSQL on NFS when Oracle > > has no problem with it (and don't tell me that

Re: [GENERAL] attempting to install tds_fw-master on redhat

Mammarelli, Joanne T wrote: > Installed postgresql-devel .. getting closer .. > > Installed Packages > postgresql.x86_64 9.2.13-1.el7_1 > @rhel7-x86_64-2015-07 > postgresql-devel.x86_64 9.2.13-1.el7_1 > @rhel-x86_64-server-7 > postgresql-libs.x86_64

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

Doiron, Daniel wrote: > I’m troubleshooting a schema and found this: > > Indexes: > "pk_patient_diagnoses" PRIMARY KEY, btree (id) > "index_4341548" UNIQUE, btree (id) > "idx_patient_diagnoses_deleted" btree (deleted) > "idx_patient_diagnoses_diagnosis_type_id" btree (diagnosis_typ

Re: [GENERAL] can postgres run well on NFS mounted partitions?

John McKown wrote: > All of the above make we curious about using NFS for the data files, but > having the WAL files on a > local, perhaps SSD, device.​ I am not knowledgeable about WAL. Of course, I > don't know why the OP wants > to put the database files on an NFS. If the data file storage do

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

Jeremy Harris wrote: > On 13/11/15 10:49, Thomas Kellerer wrote: >>> These indexes were *not* created by PostgreSQL. >>> We are not Oracle. >> >> Well, Oracle does not create indexes on its own either - it has the same >> strategy as Postgres: >> Indexes are only created automatically for primary

Re: [GENERAL] Can SET_VARSIZE cause a memory leak?

Николай Бабаджанян wrote: > I didn't find an easy way to convert ucs-2 bytea to utf-8, so I decided to > write a C-function. Since > ucs-2 is has fixed symbol size of 2 bytes the output bytea size may differ. > > I do the following: > > bytea *result= (bytea *) palloc0(VARSIZE(in_by

Re: [GENERAL] Changelog version from 8.1.2 to 9.3.6

Yogesh Sharma wrote: > I have doubt regarding release notes of all versions. > As per release notes, below change logs are mentioned in all versions. > > "(8.3.8,8.4.1,8.2.14) Make LOAD of an already-loaded loadable module into a > no-op (Tom Lane)" > 1. What is meaning of above lines? > 2. This

Re: [GENERAL] Hot disable WAL archiving

Job wrote: > is there a way in Postgresql-9.5 to disable temporarily WAL archiving to > speed up pg_bulkload with > restarting database engine? You can set 'archive_command=/bin/true' and reload, then no WAL archives will be written. Make sure to perform a base backup as soon as your bulk load i

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

Vlad Arkhipov wrote: > I have a constraint that requires a table to be locked before checking > it (i.e. no more than 2 records with the same value in the same column). > If I lock the table in the SHARE ROW EXCLUSIVE mode, any vacuuming (or > autovacuuming) process prevents me from checking the co

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

Sameer Kumar wrote: > On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov wrote: >> I am running PostgreSQL 9.5. >> >> CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT); >> >> The constraint that the data must satisfy is `there is no more than 3 >> records with the same name`. >> >> I am no

Re: [GENERAL] Slow SQL?

haman...@t-online.de wrote: Bjørn T Johansen wrote: >> I am trying to move a small system from Oracle to PostgreSQL and I have come >> upon a sql that runs >> really slow compared to on the Oracle database and I am not able to >> interpret why this is slow. > I have experienced that some subquer

Re: [GENERAL] Slow SQL?

Bjørn T Johansen wrote: > Thx for your suggestions. Tried to use NOT EXISTS and the query was about > half a second quicker so not > much difference... > But when I try to run the 3 queries separately, then they are very quick, 2 > barely measurable and the > third takes about 1,5 seconds. The un

Re: [GENERAL] jdbc 9.4-1208 driver for PostgreSQL 9.5?

Joek Hondius wrote: > (I hope i am on the right list) pgsql-jdbc would have been the perfect list. > jdbc.postgresql.org lists version 9.4 build 1208 as the lastest. > Is this the correct version to use with PostgreSQL 9.5 (or even 9.6-beta)? > I cannot find info on this elsewhere. Yes, you shou

Re: [GENERAL] Duplicate data despite unique constraint

Jonas Tehler wrote: > We’re running Postgresql 9.4.5 on Amazon RDS. One of our tables looks > something like this: > > > CREATE TABLE users > ( > ... > email character varying(128) NOT NULL, > ... > CONSTRAINT users_email_key UNIQUE (email) > ) > > Despite this we have rows with very si

Re: [GENERAL] Unable to create oracle_fdw (foreign data wrapper) extension

Arun Rangarajan wrote: > But when I try to create the extension, I get the following error: > > postgres=# create extension oracle_fdw; > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to th

Re: [GENERAL] Database fixed size

Adir Shaban wrote: > Is there anyway to limit a database size? > For example, I need to create a database for user X and I don't want it to > use more than 5 GB. You can create a tablespace on a device with limited size. Then you can create the database on that tablespace. Yours, Laurenz Albe -

Re: [GENERAL] Restricted access on DataBases

Durumdara wrote: [...] > --- login with postgres: [...] > ALTER DEFAULT PRIVILEGES > GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER > ON TABLES > TO u_tr_db; > > login with u_tr_main: > > create table t_canyouseeme_1 (k int); > > login

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

Kevin Grittner wrote: > Sent: Tuesday, October 11, 2016 10:00 PM > To: Jason Dusek > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES > > On Tue, Oct 11, 2016 at 2:29 PM, Jason Dusek wrote: > >> I notice the following oddity: > >> =# CREAT

Re: [GENERAL] journaled FS and and WAL

t.dalpo...@gmail.com wrote: > two question related to the WAL. > > 1) I read in the doc that journaled FS is not important as WAL is > journaling itself. But who garantees that the WAL is written correctly? > I know that it's sequential and a partial update of WAL can be discarded > after a res

Re: [GENERAL] Collations and codepages

Raimo Jormakka wrote: > In Windows 7, and using PostgreSQL 9.4.5, the collation gets set to > "English_United States.1252" when > I select the "English, United States" locale in the installer. In Linux, the > collation is set to > "en_US.UTF-8". The encoding is set to UTF-8 in both instances. > >

Re: [GENERAL] journaled FS and and WAL

t.dalpo...@gmail.com wrote: > I don't mind about performance but I absolutely mind about reliability, > so I was thinking about the safest setting of linux FS and postgresql I > can use. Sure, use journaling then. I do it all the time. Yours, Laurenz Albe -- Sent via pgsql-general mailing list

Re: [GENERAL] Sequences / Replication

Jonathan Eastgate wrote: > We're seeing some odd behaviour from a PostgreSQL group - one running as > primary and the other as a > hot slave using streaming replication. > > When a failover event occurs and we switch to the hot slave as primary > sequences in tables jump by 33 > - so where the l

Re: [GENERAL] postgres_fdw : disable extended queries

Nicolas Paris wrote: > I have a 9.6 pg instance, and I am trying to link a foreign postgresql > database that do not accept > extended queries. (only simple queries > https://www.postgresql.org/docs/current/static/protocol.html ) > > When I run a query against the foreign pg instance thought pos

[GENERAL] Re: What is the best thing to do with PUBLIC schema in Postgresql database

Patricia Hu wrote: > Since it could potentially be a security loop hole. So far the action taken > to address it falls into > these two categories: > > drop the PUBLIC schema altogether. One of the concerns is with some of > the system objects that > have been exposed through PUBLIC schema p

Re: [GENERAL] Database Recovery from Corrupted Dump or Raw database table file.

Howard News wrote: > I have a raid catastrophe which has effectively blitzed a cluster data > directory. I have several pg_dump backups but these will not restore > cleanly. I assume the disk has been failing for some time and the > backups are of the corrupted database. > > Using a selective pg_

Re: [GENERAL] Surviving connections after internet problem

Durumdara wrote: > Linux server, 9.4 PG, Windows clients far-far away. > > They called us that they had an "internet reset" at 13.00, but many client > locks are alive now > (14:00). > I checked server status, and and saw 16 connections. > > In Windows PG server I read about keepalive parameters

  1   2   3   4   5   6   7   8   9   10   >