Re: [GENERAL] Function in PostgreSQL

Sachin Srivastava wrote: > Do you know if it is possible to create a function in postgres that has > errors in the syntax of the > creation sql? It would help our conversion process if we could get all of our > various functions > present in postgres even if they aren’t in 100% working shape. Is

Re: [GENERAL] Postgresql INET select and default route ?

Tim Smith wrote: > create table test(a inet); > insert into test values ('0.0.0.0/0'); > insert into test values ('10.1.2.3'); > => select * from test; > a > --- > 0.0.0.0/0 > 10.1.2.3 > (2 rows) > > > This works as expected . > => select * from test where a <<= '10.1.2.3'; >

[GENERAL] RE: [GENERAL] how to import "where exists（subquery）" EXISTS CONDITION performance?

shili wrote: > I had saw this sentence: SQL statements that use the EXISTS condition in > PostgreSQL are very inefficient > since the sub-query is RE-RUN for EVERY row in the outer query's table. There > are more efficient ways > to write most queries, that do not use the EXISTS condition. >

Re: [GENERAL] 2 questions

anj patnaik wrote: > I've got 2 more questions. The cron job is now setup and email gets generated > with proper body. > > I've one setup with NFS which appears to work smoothly. Now soon, I will be > given a Linux VM on a > different physical server, but will retain my NFS mount. I've

Re: [GENERAL] Taking lot time

It is difficult to read your e-mails. Ramesh T wrote: >> When this thread started you said you where selecting 20 rows. > > Yes. >> Which is correct 20 rows or columns and if columns how many rows? > > Yes,I did selected 20 columns from two tables. >> Where are you measuring this time? > >

Re: [GENERAL] Query failed: ERROR: character with byte sequence 0xc2 0x96 in encoding "UTF8" has no equivalent in encoding "WIN1250"

I realize I was too short, sorry. NTPT wrote: > but how to update affected columns ? error message does not provide single > clue ( at least row name) For every table and every column in the source database that might be affected, try something like: SELECT id, col FROM tab WHERE col LIKE

Re: [GENERAL] Query failed: ERROR: character with byte sequence 0xc2 0x96 in encoding "UTF8" has no equivalent in encoding "WIN1250"

I wrote: > You can run something like this over the plain text dump: > > sed -e 's/–/-/g' dump.sql >fixed.sql Scrap that, it should of course be: sed -e 's/–/-/g' dump.sql >fixed.sql Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to

Re: [GENERAL] What could cause CREATE TEMP... "could not read block" error?

Chris Richards wrote: [had corruption in pg_depend indexes] > I've observed the problem on other than the "pg_depend" table. A normal table > (called "blocks") had > the problem. A "REINDEX TABLE blocks" did not fix the problem, however a > "VACUUM FULL" did "fix" it. Did you dump/remove

Re: [GENERAL] Query failed: ERROR: character with byte sequence 0xc2 0x96 in encoding "UTF8" has no equivalent in encoding "WIN1250"

NTPT wrote: > I need help. > > pg_exec(): Query failed: ERROR: character with byte sequence 0xc2 0x96 in > encoding "UTF8" has no > equivalent in encoding "WIN1250" > > It is a strange. First there was a database with latin2 encoding. > > to this database connect an aplicaton with "set client

Re: [GENERAL] Query failed: ERROR: character with byte sequence 0xc2 0x96 in encoding "UTF8" has no equivalent in encoding "WIN1250"

NTPT wrote: > but how to solve this in existing database ? Either update all affected columns in the source database or edit the database dump... Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] [ADMIN] How to drop stats on table

Sridhar N Bamandlapally wrote: > the actual issue is, when > > 1. temp table is created with rows > 2. stats/analyze on table (tmp1) > 3. table dropped (tmp1) > > but in stats related catalog tables a blot is created What is a blot in this context? Are you talking about "bloat"? > In this

Re: [GENERAL] What could cause CREATE TEMP... "could not read block" error?

Chris Richards wrote: > Adrian is correct. This worked by itself whereas using it in the creation of > the temporary table > failed. > mdb-> SELECT pq.* FROM policyqueue AS pq > mdb-> JOIN seed_progress AS sp ON pq.id =sp.polidx; > > I checked the query Albe suggested; there were

Re: [GENERAL] What could cause CREATE TEMP... "could not read block" error?

Chris Richards wrote: > Howdy. I have two tables that I'm joining together and using the result to > create a temporary table. > Performing the join operation works fine; it produces 0 rows (as expected). > If I prepend "CREATE TEMP > tmp_policyqueue AS" to the SELECT then it generates this

Re: [GENERAL] [ADMIN] How to drop stats on table

Sridhar N Bamandlapally wrote: > is there any feature available in postgres to drop stats on table? What about DELETE FROM pg_catalog.pg_statistic WHERE starelid = Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] pg_restore encounter deadlock since PostgreSQL bringing up

zh1029 wrote: > While start PostgreSQL(9.3.6) and execute pg_restore soon after PostgreSQL > bringing up. I encounter pg_restore failure because of deadlock detected. pg_restore will not deadlock with itself or system startup. Set log_connections to "on", then you will see who connected and

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

anj patnaik wrote: > How do you tell if a database is corrupted? Are there specific error > messages/symptoms to look for? That's actually a pretty tough question. The standard test is to run "pg_dumpall", see if it finishes without error and if the dump can be restored without error. That

Re: [GENERAL] referencing other INSERT VALUES columns inside the insert

Geoff Winkless wrote: > I know that this is something that can't be done... > > CREATE TABLE test (c1 int default 0, c2 int default 0, c3 int default 0); > INSERT INTO test (c1, c2, c3) VALUES (3, 7, c1 * c2); > > Is there a known trick to work around it (so that the values inserted into c1 >

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] 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

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

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

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 >

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 Oracle do

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] 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

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] 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

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: >> 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

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

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

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

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

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

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: > >

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

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 > >

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] 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

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,

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

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

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

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 index. I

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

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 mentions:

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

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 mentions:

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.service;

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] 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

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) [...] could

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,'(\n)',

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, as it

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 t...@sss.pgh.pa.us wrote: Douglas Stetner stet...@icloud.com 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

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 replication

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 inserted

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] 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] 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: Dumping the

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] 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

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 system

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

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] 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] 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 date NOT NULL

[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] 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 that

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 laurenz.a...@wien.gv.at wrote: Mitu Verma wrote: Following error is continuously seen with the postgreSQL database which we are using at customer site. Current Errors observed

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 select

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 complementary?

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 with

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 that

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 inner

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)

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 resolve

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: operator

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 Albe

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

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 laurenz.a...@wien.gv.at 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 should be ok. I would

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 a

[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 good

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

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

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_Server3=#

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 zhouqq.postg...@gmail.com wrote: On Thu, Apr 16, 2015 at 5:09 AM, Geoff Speicher gspeic...@umtechx.com wrote: ZFS implements copy-on-write, so when PostgreSQL modifies a block on disk, the filesystem writes a new block

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

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. I

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

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 base =

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

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] Sequences not moved to new tablespace

Guillaume Drolet wrote: If you want to move a whole database to a different tablespace (the only reason I can think of for doing what you are trying to so), use the command ALTER DATABASE ... SET TABLESPACE ... Thanks Laurenz. I tried your suggestion: psql -U postgres -c ALTER DATABASE

Re: [GENERAL] Sequences not moved to new tablespace

Guillaume Drolet wrote: If you want to move a whole database to a different tablespace (the only reason I can think of for doing what you are trying to so), use the command ALTER DATABASE ... SET TABLESPACE ... Thanks Laurenz. I tried your suggestion: psql -U postgres -c ALTER DATABASE

Re: [GENERAL] Sequences not moved to new tablespace

Guillaume Drolet wrote: Digging a little more, I found that not only sequences were not moved but also many tables in pg_catalog are still in my old tablespace. This is expected since the query in the SQL files I used to move the tables and indexes had a WHERE clause like this: SELECT '

Re: [GENERAL] Can I habe multi table indices?

Andreas wrote: can I habe multi table indices to ensure that certain IDs can only appear in on relation? Like table_0(id int), table_1(id int), table_2(id int) rel_1_to_0( id_1 int, id_0 int ) rel_2_to_0( id_2 int, id_0 int ) An id of table_0 should only exist either in rel_1_to_0

<    1   2   3   4   5   6   7   8   9   10   >