Hi,
In my product in some rare cases , DB connections could not be open, it is
stuck at following location:-
It is stuck at following location :-
sendto(3, "p\0\0\0(md5de8bdf202e563b11a4384ba5"..., 41, 0, NULL, 0) = 41
<0.12>rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 <0.05>poll([{fd=3,
Apologies for the long email.
I would appreciate some advice designing a database which
can become very complicated. The purpose of the database is to be a
source for bibliometric research.
Some background: I work with various sources:
* The largest source consists of tagged delimited text fil
Hello Raghavendra,
I think probably psql is a problem here. WIthout -W (force password
before connect) option psql has no way knowing if this user needs a
password to connect to the given database. So, psql first attempts a
connection to the database without a password (if -W is not
specified), wh
Greetings,
I have a domain specific language implementation that ends up as sql after
an automatic transformation/code generation phase.
The complexity of the generated sql queries can be significant, with lots
of self joins and many other criteria injected into sql.
I've just noticed that some p
If I have a simple table with an id as a primary key that is a serial column
and a column to keep track of a total_count for a particular id, what method
would provide the fastest way to increment the total_count in the shortest
amount of time and minimize any locking?
id serial
total_count i
On Wed, May 8, 2013 at 8:45 AM, Tyson Maly wrote:
> If I have a simple table with an id as a primary key that is a serial column
> and a column to keep track of a total_count for a particular id, what method
> would provide the fastest way to increment the total_count in the shortest
> amount of t
Tyson Maly wrote:
> If I have a simple table with an id as a primary key that is a serial column
> and a column to keep
> track of a total_count for a particular id, what method would provide the
> fastest way to increment the
> total_count in the shortest amount of time and minimize any locking?
Seref Arikan writes:
> I've just noticed that some parts of the autogenerated queries can be
> functions on their own. Would moving these parts to their own functions
> help the query planner?
It's difficult to answer that without a lot more detail than you've
provided, but my offhand guess would
Thanks Tom,
I am happy with a 10K feet level discussion of the approach, and your
response is just what I was looking for.
Regards
Seref
On Wed, May 8, 2013 at 3:05 PM, Tom Lane wrote:
> Seref Arikan writes:
> > I've just noticed that some parts of the autogenerated queries can be
> > functi
On 08/05/13 21:21, Johann Spies wrote:
> Basically my request is for advice on how to make this database as
> fast as possible with as few instances of duplicated data while
> providing both for the updates on level 0 and value added editing on
> level 1.
>
> Regards
> Johann
Hi Johann.
Firstly,
On 08/05/13 23:40, Seref Arikan wrote:
> Greetings,
>
> I have a domain specific language implementation that ends up as sql
> after an automatic transformation/code generation phase.
> The complexity of the generated sql queries can be significant, with
> lots of self joins and many other crite
This is a small, but weird problem. Completely regular table :
- requesting count in pgadmin shows 3124448 rows
- running SELECT count(*) via the query tool returns 5997620
Why is there a difference? There's nothing remotely remarkable about the
table.
I've run a full database VACUUM just to be c
On Wed, May 8, 2013 at 9:05 AM, Tom Lane wrote:
> Seref Arikan writes:
>> I've just noticed that some parts of the autogenerated queries can be
>> functions on their own. Would moving these parts to their own functions
>> help the query planner?
>
> It's difficult to answer that without a lot mor
Oops, got it. Count(*) includes the rowcount of tables that inherit from
the table queried? I wasn't counting on that.
On Wed, May 8, 2013 at 4:44 PM, Tobias Larsen wrote:
> This is a small, but weird problem. Completely regular table :
> - requesting count in pgadmin shows 3124448 rows
> - run
On Wed, May 8, 2013 at 4:55 PM, Amit Langote wrote:
> Hello Raghavendra,
>
> I think probably psql is a problem here. WIthout -W (force password
> before connect) option psql has no way knowing if this user needs a
> password to connect to the given database. So, psql first attempts a
> connection
Merlin Moncure writes:
> In my experience virtually no useful functions are inlined by the
> planner. For example, with function:
> create function f(text) returns date as $$
> select to_date($1, ''); $$
> language sql stable; /* immutable doesn't help */
> I see about 4x time differenc
Tobias Larsen writes:
> Oops, got it. Count(*) includes the rowcount of tables that inherit
> from the table queried? I wasn't counting on that.
To prevent inheriting tables from being scanned as well, run your
query as;
select count(*) from ONLY footable;
> On Wed, May 8, 2013 at 4:44 PM,
| I'm using PG 9.1.9 with a client application using various versions of
the
| pgsqlODBC driver on Windows. Cursors are used heavily, as well as some
pretty
| heavy trigger queries on db writes which update several materialized
views.
|
| The server has 48GB RAM installed, PG is configured for 12GB
I've tried several times to upgrade a test database (with real data,
~500 GB) from 9.1 to 9.2 using pg_upgrade and every time it fails with
the same error. I've tried a few different options to pg_upgrade but
always the same result. Nothing really useful has turned up in
Google. Any thoughts? C
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Evan D. Hoffman
> Sent: Wednesday, May 08, 2013 2:27 PM
> To: Postgresql Mailing List
> Subject: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9
> to 9
On Wed, May 8, 2013 at 11:12 AM, Tom Lane wrote:
> Merlin Moncure writes:
>> In my experience virtually no useful functions are inlined by the
>> planner. For example, with function:
>
>> create function f(text) returns date as $$
>> select to_date($1, ''); $$
>> language sql stable; /*
Merlin Moncure writes:
> odd that stable function is inlined but immutable isn't!
Well, it knows that the expansion to to_date() would only be stable not
immutable (because to_date depends on some GUC settings), so doing the
expansion could change the behavior, eg by preventing constant-folding.
Hi all,
I have a Primary Standby setup with streaming replication.
Trigger is created on a table, and all it does it to log a message.
The trigger works as expected on Primary, however, I don't see the same on
standby.
I alter the table to have ENABLE ALWAYS TRIGGER, I verified the setting on
bo
> -Original Message-
> From: Evan D. Hoffman [mailto:evandhoff...@gmail.com]
> Sent: Wednesday, May 08, 2013 3:35 PM
> To: Igor Neyman
> Subject: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" -
> 9.1.9 to 9.2.4
>
> Looks like it IS the same OID every time, referencing an ind
Well, each time it fails it refers to the file
"/var/lib/pgsql/9.1/data/base/16406/3016054", but that's not the file
associated with OID 2938685.
Here's the output of that query:
db=# Select relname from pg_class where relfilenode = 3016054::oid;
relname
-
(0 rows)
db=#
On Wed, May 8,
ning chan writes:
> Hi all,
>
> I have a Primary Standby setup with streaming replication.
> Trigger is created on a table, and all it does it to log a message.
>
> The trigger works as expected on Primary, however, I don't see the same on
> standby.
>
> I alter the table to have ENABLE ALWAYS T
"Evan D. Hoffman" writes:
> (The file referenced in the error was
> /var/lib/pgsql/9.1/data/base/16406/3016054)
I'm not sure about how pg_upgrade manages its output, but it seems
entirely possible that that was the last file successfully transferred,
not the one the error occurred on.
> Looks li
> -Original Message-
> From: Evan D. Hoffman [mailto:evandhoff...@gmail.com]
> Sent: Wednesday, May 08, 2013 4:22 PM
> To: Igor Neyman
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" -
> 9.1.9 to 9.2.4
>
> Well, each time it fails
On 09/05/13 02:47, Merlin Moncure wrote:
On Wed, May 8, 2013 at 9:05 AM, Tom Lane wrote:
Seref Arikan writes:
I've just noticed that some parts of the autogenerated queries can be
functions on their own. Would moving these parts to their own functions
help the query planner?
It's difficult t
A minor detail in 9.2.4, but I noticed that the pg_upgrade flag for
superuser, -u, does not get carried to a -U flag on the vacuumdb commands
written to analyze_new_cluster.sh.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www
Let's say you have a database which is owned (as well as all the contents
within it) by the postgres internal user.
Having created or inherited a mess, how do you fix it?
database1=# REASSIGN OWNED BY postgres TO foo ;
ERROR: cannot reassign ownership of objects owned by role postgres because
t
On Wed, May 8, 2013 at 2:04 PM, Tom Lane wrote:
> Merlin Moncure writes:
>> odd that stable function is inlined but immutable isn't!
>
> Well, it knows that the expansion to to_date() would only be stable not
> immutable (because to_date depends on some GUC settings), so doing the
> expansion cou
On 09/05/13 09:35, Merlin Moncure wrote:
[...]
More oddness -- when I wrap, say, random() with stable function, I get
unique value per returned row, but same value across the set when
wrapped with immutable.
[..]
That part I think I know (but, I'm often wrong!).
By saying it is immutable, yo
Hi,
The manual mentions that SERIALIZABLE transactions may abort with error 40001,
in which case the client application is supposed to retry the transaction.
I've been
stress testing an application by issuing lots of concurrent requests, and sure
enough,
every now and then I get back those 4000
On May 8, 2013, at 21:14, Tyson Maly wrote:
>
> The simple update is one I considered, but I think if I put it into a stored
> procedure it should run faster
>
Well, you would partially circumvent the query planner, but you would also
circumvent any optimisation said query planner would be
> The manual mentions that SERIALIZABLE transactions may abort with error 40001,
> in which case the client application is supposed to retry the transaction.
> I've been
> stress testing an application by issuing lots of concurrent requests, and
> sure enough,
> every now and then I get back tho
On Wed, May 8, 2013 at 05:05:05PM -0400, Ray Stell wrote:
> A minor detail in 9.2.4, but I noticed that the pg_upgrade flag for
> superuser, -u, does not get carried to a -U flag on the vacuumdb commands
> written to analyze_new_cluster.sh.
OK, let me look at this issue.
--
Bruce Momjian
How often are these updated? Once an hour, once a minute, once a
second, a thousand times a second?
If it's not more than once a second I would look at eager materialized
views as a possibility for handing this.
http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views#Eager_Materialized
Jon Smark wrote:
> The manual mentions that SERIALIZABLE transactions may abort with
> error 40001, in which case the client application is supposed to
> retry the transaction. I've been stress testing an application
> by issuing lots of concurrent requests, and sure enough, every
> now and then
39 matches
Mail list logo
