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
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
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
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
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
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:
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
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.
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+)?
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:
> >
>
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
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
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:
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
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
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
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
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
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
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
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
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
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
Hello,
I have a table with the following structure:
nn=> \d documents
Table "public.documents"
Column | Type | Modifiers
---+--+
id| integer |
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
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
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
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
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
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
On 02/04/2012 01:52, Martin Gregorie wrote:
> BTW, why use document_chunks when a text field can hold megabytes,
> especially if they will be concatenated to form a complete document
> which is then edited as a whole item and before being split into chunks
> and saved back to the database?
... b
2012/4/2 Ondrej Ivanič :
> Hi,
>
> On 2 April 2012 08:38, Ivan Voras wrote:
>> db=> set enable_seqscan to off;
>
>
>
>>
>> This huge cost of 100 which appeared out of nowhere in the
>> EXPLAIN output and the seq scan worry me - where did that
Hi,
I have documents which are divided into chunks, so that the (ordered)
concatenation of chunks make the whole document. Each of the chunks may
be edited separately and past versions of the chunks need to be kept.
The structure looks fairly simple:
CREATE TABLE documents (
id SERIAL PRIMAR
On 28/02/2012 17:57, mgo...@isstrucksoftware.net wrote:
> Our application runs on Windows, however we have been told that we can
> pick any OS to run our server on. I'm thinking Linux because from
> everything I've read, it appears to be a better on performance and there
> are other features like
On 28/02/2012 18:17, Rich Shepard wrote:
> On Tue, 28 Feb 2012, mgo...@isstrucksoftware.net wrote:
>
>> If we move to Linux, what is the preferred Linux for running Postgres
>> on. This machine would be dedicated to the database only.
>
> Michael,
>
> There is no 'preferred' linux distribution
I was looking for some PostgreSQL promotional material and found this:
http://imgur.com/4VUUw
I would very much like to get a high-res version of this image - does
anyone here have it?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription
On 18 November 2011 01:20, Tom Lane wrote:
> Ivan Voras writes:
>> Is there any way to make _PG_init() called earlier, e.g. as soon as
>> the session is established or at database connection time, something
>> like that?
>
> Preload the library --- see sha
On 17 November 2011 19:02, Tom Lane wrote:
> Ivan Voras writes:
>> Ideally, the C module would create its own "custom variable class,"
>> named e.g. "module", then define some setting, e.g. "module.setting".
>> The users would then execute an
I'm writing a custom C function and one of the things it needs to do is
to be configured from the SQL-land, per user session (different users
have different configurations in different sessions).
I have found (and have used) the SET SESSION command and the
current_setting() function for use with c
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
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
>
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
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
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.
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
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
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
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
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
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
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
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
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
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
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
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
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
On 09/01/10 16:13, Igor Neyman wrote:
-Original Message-
From: Raymond C. Rodgers [mailto:sinful...@gmail.com]
Sent: Tuesday, August 31, 2010 7:56 PM
To: pgsql-general@postgresql.org
Subject: Table update problem works on MySQL but not Postgres
update mydemo set cat_order = cat_orde
On 08/19/10 20:19, Benjamin Smith wrote:
> On Wednesday, August 18, 2010 04:58:08 pm Joshua D. Drake wrote:
>> Well if you are just using it for updates to the schema etc... you
>> should only need to launch a single connection to each database to make
>> those changes.
>
> And that's exactly the
On 26.7.2010 12:43, AlannY wrote:
> Hi there.
>
> I have a huge database with several tables. Some tables have statistics
> information. And it's very huge. I don't want to loose any of this data.
> But hard drives on my single server are not eternal. Very soon, there will
> be no left space. And
On 07/16/10 02:23, Tom Lane wrote:
> Howard Rogers writes:
>> I have 10 million rows in a table, with full text index created on one
>> of the columns. I submit this query:
>
>> ims=# select count(*) from search_rm
>> ims-# where to_tsvector('english', textsearch)
>> ims-# @@ to_tsquery('english'
On 15 June 2010 18:22, Janning wrote:
>> The figures are ok if the tests were done on a single drive (i.e. not
>> your RAID-0 array).
>
> Ahh, I meant raid-1, of course. Sorry for this.
> I tested my raid 1 too and it looks quite the same. Not much difference.
This is expected: a RAID-1 array (
On 06/15/10 14:59, Janning wrote:
> Hi all,
>
> as we encountered some limitations of our cheap disk setup, I really would
> like to see how cheap they are compared to expensive disk setups.
>
> We have a 12 GB RAM machine with intel i7-975 and using
> 3 disks "Seagate Barracuda 7200.11, ST31500
hello,
I think I have a problem with tsearch2 configuration I'm trying to use.
I have created a text search configuration as:
--
CREATE TEXT SEARCH DICTIONARY hr_ispell (
TEMPLATE = ispell,
DictFile = 'hr',
AffFile = 'hr',
StopWords = 'hr'
);
CREATE TEXT SEARCH CONFIGURATION publ
On 05/23/10 07:17, Craig Ringer wrote:
> On 23/05/10 10:40, Ivan Voras wrote:
>> Hello,
>>
>> I have a schema which tracks various pieces of information which would
>> need to be globally searchable.
>
> If systems that exist outside the database its self a
On 05/23/10 18:03, Andy Colson wrote:
> On 05/22/2010 09:40 PM, Ivan Voras wrote:
>> 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 searc
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
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
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
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
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
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
On 9 April 2010 18:21, Greg Smith wrote:
> Ivan Voras wrote:
>>
>> I'd like to ask about the asynchronous nature of upcoming replication
>> implementation in 9.0 - what guarantees does it give with regards to
>> delays and latency? E.g. do COMMITs "finish&q
Hello,
I'd like to ask about the asynchronous nature of upcoming replication
implementation in 9.0 - what guarantees does it give with regards to
delays and latency? E.g. do COMMITs "finish" and return to the caller
before or after the data is sent to the slave? (being asynchronous, they
probably
On 01/21/10 16:09, John Mitchell wrote:
So am I to presume that the current stable version of postgres (before
8.5) does require extra locking?
There is currently (before 8.5) no official replication mechanism in
PostgreSQL. There are some 3rd party implementations, for which
information can
Leigh Dyer wrote:
Hi,
For years now I've simply backed up my databases by doing a nightly
pg_dump, but since we added the ability for users to import binary files
in to our application, which are stored in a bytea fields, the dump
sizes have gone through the roof — even with gzip compression, th
John R Pierce wrote:
Andre Lopes wrote:
Hi,
I need to know if there is something like Oracle Forms in the Open
Source world that works with PostgreSQL.
If do you know something, please let me know.
perhaps OpenOffice Data could do what you need. I'm not real familiar
with Oracle Forms, b
A. Kretschmer wrote:
In response to Tom :
I have a big table that is used for datalogging. I'm designing
graphing interface that will visualise the data. When the user is
looking at a small daterange I want the database to be queried for all
records, but when the user is 'zoomed out', looking at
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
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
Peter Eisentraut wrote:
> Craig Ringer wrote:
>> So - it's potentially even worth compressing the wire protocol for use
>> on a 100 megabit LAN if a lightweight scheme like LZO can be used.
>
> LZO is under the GPL though.
But liblzf is BSD-style.
http://www.goof.com/pcg/marc/liblzf.html
sign
Greg Smith wrote:
On Sat, 26 Jul 2008, Zoltan Boszormenyi wrote:
Zoltan Boszormenyi �rta:
These three settings were also set with "sysctl -w ..." to take effect
immediately.
Rebooting FreeBSD solved it. Huh? Is it really like W#&@$#&?
Looks like the PostgreSQL documentation here (
http://w
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
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
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
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
86 matches
Mail list logo
