That is why I need to lock the table before. The transactions are
running at the READ COMMITTED isolation level.
On 06/22/2016 06:49 PM, Albe Laurenz wrote:
But be warned that this will only work if all transactions involved use
the isolation level SERIALIZABLE.
--
Sent via pgsql-general
.
On 06/22/2016 05:20 PM, Sameer Kumar wrote:
On Wed, Jun 22, 2016 at 5:10 PM Vlad Arkhipov <arhi...@dc.baikal.ru
<mailto:arhi...@dc.baikal.ru>> wrote:
Hello,
I have a constraint that requires a table to be locked before checking
it (i.e. no more than 2 records with th
Hello,
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 constraint. What
PostgresSQL has support for master-slave replication, but the synchronous
streaming replication
http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION
only supports a single synchronous slaves. You can have multiple slaves,
but only one will be synchronously
of megabytes (indexes included).
Can anyone please explain why this is happening and how we can get around
it?
Thank you,
Vlad
On Fri, Feb 1, 2013 at 11:01 AM, Pavan Deolasee pavan.deola...@gmail.comwrote:
Do you know for sure that its the master table that generating all the
vacuum traffic ? What about the partition tables ? Do they get any
updates/deletes ? It might be useful if you could turn autovacuum
logging
On Fri, Feb 1, 2013 at 1:08 PM, Pavan Deolasee pavan.deola...@gmail.comwrote:
On Fri, Feb 1, 2013 at 3:24 PM, Vlad Bailescu v...@mojitosoftware.comwrote:
Stats show only 3 tables get frequently autovacuumed
Table Name Tuples inserted Tuples updated Tuples deleted Tuples HOT
updated
On Fri, Feb 1, 2013 at 2:40 PM, Pavan Deolasee pavan.deola...@gmail.comwrote:
On Fri, Feb 1, 2013 at 5:19 PM, Vlad Bailescu v...@mojitosoftware.comwrote:
Pavan, it seems there's a small issue with copy-paste and column
text-align. Table sizes are:
4136 kB 2448 kB 2336 kB
Ah OK. I
On 01/14/2013 05:15 PM, Condor wrote:
Hello,
from some time I have a very strange problem with my postgresql 9.2.2
64bit.
I make a few changes with an plp function:
BEGIN
UPDATE table SET X = X where id = aid;
UPDATE table_2 SET Y=Y where id = aid;
IF aid 0 THEN
SELECT INTO ids id
Hello list,
I need to make a copy of certain rows in table A and remember the
original pkey and new pkey in some temporary table B. Basically the copy
statement isINSERT INTO a SELECT * FROM a where a.x=y;
I guess I can do it with a plpgsql function and a FOR loop statement,
because I
On 11/26/2012 07:15 PM, Merlin Moncure wrote:
I don't think you need a CTE or a loop unless you want to delete the
old rows. Why not (hard to say exactly how it will work):
INSERT INTO a(old_id, new_id, ...)
SELECT id, new_id(), ...
from a;
?
Well, the table A, in which I'm copying rows,
RAID10
-- vlad
On 11/24/2012 3:17 PM, Gavin Flower wrote:
Curious, what is your RAID configuration?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 11/24/2012 10:15 AM, Rafal Pietrak wrote:
Some improvement in passwords safety could be gained, if the database
table access methods (e.g. SELECT...) provided means to limit that
access to just one entry at a time, and return results only when
(password) column hash was equal for a single
, with no apparent reason (i.e.:
no increased load or anything), then goes down in a few secs, or stays
until postgres restarted.
at this point the only thing that does help on app side is when I force
frequent re-connection to postgresql (i.e. not letting postmaster live too
long)
-- Vlad
On Wed
what pgbouncer mode, and how large is your pool.
'-M prepared' produces normal results, while '-M simple' results in 40% sys
cpu. '-M extended' is somewhere in between.
I'm running it as 60 clients, 2 threads.
-- Vlad
with regular queries coming from our app.
-- Vlad
On Wed, Nov 21, 2012 at 10:45 AM, Merlin Moncure mmonc...@gmail.com wrote:
On Wed, Nov 21, 2012 at 10:43 AM, Jeff Janes jeff.ja...@gmail.com wrote:
On Wed, Nov 21, 2012 at 7:29 AM, Vlad Marchenko marche...@gmail.com
wrote:
update on my problem
Merlin,
On Wed, Nov 21, 2012 at 2:17 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Wed, Nov 21, 2012 at 12:17 PM, Vlad marche...@gmail.com wrote:
It turned out we can't use transaction mode, cause there are prepared
statement used a lot within code, while processing a single http request
to this database), which would occupy connection for longer time, but do
not create any race condition... So having pool size at 8 may be too slim .
It's a single database.
-- Vlad
On Wed, Nov 21, 2012 at 12:04 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Wed, Nov 21, 2012 at 11:56 AM, Vlad marche
ok, understood.
I need to give some more thoughts to if it's possible for us to switch to
transaction mode from app standpoint of view.
if yes, then setting pool size to 20 (for 8 cores) sounds OK?
-- Vlad
for a while, then
it's much easier to do.
-- vlad
On 11/19/12 8:33 AM, Merlin Moncure wrote:
On Sun, Nov 18, 2012 at 4:24 PM, Jeff Janes jeff.ja...@gmail.com wrote:
On Fri, Nov 16, 2012 at 12:13 PM, Vlad marche...@gmail.com wrote:
ok, I've applied that patch and ran. The stall started around 13
available, and some additional overhead on re-connection), but no
single case of high-sys-cpu stall.
I can not completely rule out the possibility of some left-overs
(unfinished transaction?) remain after serving http request, which, in the
absence of connection caching, are discarded for sure
-- Vlad
inside which I can't share
with public.
-- Vlad
yup. this problem doesn't smell like lwlock issues. typically there
the problem manifests as low cpu performance, everybody waits.
classic spinlock contention (at least from what i've seen) is very
high *userspace* cpu utilization and low work
what would pgbouncer do in my case? Number of connections will decrease,
but number of active clients won't be smaller. As I understand the latter
ones are that important.
-- Vlad
On Fri, Nov 16, 2012 at 2:31 PM, Merlin Moncure mmonc...@gmail.com wrote:
first thoughts:
no single thing
interested in getting to the bottom of the issue vs
alleviating it with connection poller, so I might setup a server on
Centos 6.3 (kernel ver 2.6.32) and give it a try.
Alternatively, if anyone have ideas on playing with sysctl parameters
related to scheduler - please let me know.
-- vlad
to put pgbouncer in place and repeat, also though this will take
longer time to setup.
-- Vlad
://archives.postgresql.org/pgsql-hackers/2012-06/msg01588.php)
to see if things improve. This is dangerous, and could crash your
server/eat your data, so fair warning.
merlin
-- vlad
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
uniform.
Thanks for your patience.
oh no, thank you for trying to help me to resolve this issue.
-- vlad
there is no big spike of queries that cause that, queries come in
relatively stable pace. It's just when the higher rate of queries coming,
the more likely this to happen. yes, when stall happens , the active
queries pile up - but that's the result of a stall (the server reacts slow
on a
and it experienced the same problem. So since PITR
db can only perform read-only queries, there is no write-locks (except
maybe when pitr is playing wal records from the master?), nevertheless SYS
CPU jumped sky.
-- Vlad
Is this still true? Can we capture strace from one of the high
/proc/sys/vm/zone_reclaim_mode
I presume you wanted cat instead of echo, and it shows 0.
-- vlad
sorry - no panics / errors in the log...
-- Vlad
= MIN_DELAY_MSEC;
-- vlad
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
= 3000
wal_level = hot_standby
fsync = off
checkpoint_segments = 64
checkpoint_timeout = 15min
effective_cache_size = 8GB
default_statistics_target = 500
-- Vlad
several active queries at any given time.
-- Vlad
On Wed, Nov 14, 2012 at 3:23 PM, John R Pierce pie...@hogranch.com wrote:
On 11/14/12 1:13 PM, Vlad wrote:
Postgresql 9.1.6.
Postgres usually has 400-500 connected clients, most of them are idle.
Database is over 1000 tables (across 5
Is it guaranteed that if txid2 txid1 then current_timestamp in
transaction 2 = current_timestamp in transaction 1?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 06/16/2012 02:20 AM, Tom Lane wrote:
Vlad Arkhipovarhi...@dc.baikal.ru writes:
What is the proper way of getting RULE condition and commands?
pg_get_ruledef()
regards, tom lane
It prints the whole CREATE RULE command. Is there any way to extract
WHERE condition
What is the proper way of getting RULE condition and commands? The query
below does not work.
select pg_get_expr(ev_qual, ev_class, true) as condition,
pg_get_expr(ev_action, ev_class, true) as commands
from pg_rewrite;
ERROR: bogus varno: 2
** Error **
ERROR: bogus
Hello,
As a one-off attempt to change a large table's 'bytea' column to
'text' with minimal I/O (where the 'bytea' contents is already valid
UTF8 and the database encoding is also UTF8, and the column is not
part of any index or anything involving collation), how unsafe is the
following?
UPDATE
Hello,
Is there any way of casting (reinterpreting) a varchar/text field
containing arbitrary backslashes to bytea without making an escaped
copy of the varchar/text first? In the examples below I am using a
constant E'...' for clarity, the value normally comes from a
varchar/text column in a
the domain, assuming the lot would then
behave like the aforementioned trick in the pg_convert_from
implementation, but it doesn't seem to work that way. :(
V.
On Wed, Mar 16, 2011 at 11:51 AM, Bruce Momjian br...@momjian.us wrote:
Vlad Romascanu wrote:
Hello,
Is there any way of casting
:
Bruce Momjian br...@momjian.us writes:
Vlad Romascanu wrote:
Is there any way of casting (reinterpreting) a varchar/text field
containing arbitrary backslashes to bytea without making an escaped
copy of the varchar/text first?
Well, the '\\' is being converted to '\' because of the single
Aha! :) Why haven't I used convert_from() -- my ultimate abominable
goal is to do an in-place migration of an SQL_ASCII database
(LC_TYPE/COLLATION=C) to UTF8 (LC_TYPE/COLLATION=C), where the
string data in the SQL_ASCII database is in some encoding, say LATIN1,
and where the bulk of the data is
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello,
I need to perform conversions (transcoding) between BYTEA and TEXT
columns in a UTF-8 database. I searched for existing solutions and
was unable to find one for 8.x or 9.x, so I cam up with something I'd
like to validate with the more enlightened members of this list...
Case 1:
, to the public via a stored proc
for those in my situation or who need more granular control over
autovacuuming than the autovacuum daemon does?
Thank you,
Vlad.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
PostgreSQL 8.3.3, on Win32.
I have a table with 8 fixed-size (integer-type) columns: 6 x int8, 1 x
integer, 1 x int2. All columns have storage=plain. Have explicitly set
FILLFACTOR=100 and WITHOUT OIDS just so there's no misunderstanding.
Expected row size, based solely on data types and
I'd do
du -sh /var/pgsql/data/base
rather then /var/pgsql/data
depending on how your pgsql server logging is setup, there are other
folders and/or files that might take considerable disk space under
./data/ you may want to exclude those. I find this query useful for
something like this as
is there a tentative release date (week ... month) for postgres-8.3.2 ?
Thanks!
Vlad
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
16MB before it
gets rsync'd over. I suspect no amount of poking at the standby will
root out the issue because it's happening on the primary.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
--
Vladimir (Vlad) Kosilov
Senior
/Linux
Thanks much in advance
V.
--
Vladimir (Vlad) Kosilov
Senior Systems Administrator
Contigo Systems Inc.
604.683.3106 (phone)
604.648.9886 (fax)
[EMAIL PROTECTED]
www.contigo.com
--
Sent via pgsql-general
busy db and linux/nfs
Thanks,
V.
--
Vladimir (Vlad) Kosilov
Senior Systems Administrator
Contigo Systems Inc.
604.683.3106 (phone)
604.648.9886 (fax)
[EMAIL PROTECTED]
www.contigo.com
--
Sent via pgsql-general
checks for complete segment sizes.
thanks
Ioannis Tambouras
--
Vladimir (Vlad) Kosilov
Senior Systems Administrator
Contigo Systems Inc.
604.683.3106 (phone)
604.648.9886 (fax)
[EMAIL PROTECTED]
www.contigo.com
of poking at the standby will
root out the issue because it's happening on the primary.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
--
Vladimir (Vlad) Kosilov
Senior Systems Administrator
Contigo Systems Inc.
604.683.3106 (phone
feature of 8.3? Right now the switch from 8.2 to 8.3 doesn't
seems a favorable step for the type of application that we have...
-- vlad
- Original Message -
From: Tom Lane [EMAIL PROTECTED]
To: Vlad [EMAIL PROTECTED]
Cc: PG-General pgsql-general@postgresql.org
Sent: Monday, January 28
I just tried adjusting two parameters:
enable_hashagg = off
both versions run slower, still 8.2 quicker than 8.3 (by ~ the same %)
enable_hashjoin = off
both versions run slower, still 8.2 quicker than 8.3 (by ~ the same %)
On Jan 29, 2008 10:34 AM, Clodoaldo [EMAIL PROTECTED] wrote:
Vlad
guess we see the result of concurrent
scans optimization in 8.3...
--
Vlad
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
-doing-wrong are welcomed
--
Vlad
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
and repeat
import and your test.
Regards
Pavel Stehule
-- Vlad
QUERY
PLAN
This last bit often means there's some overhead in the systems
timeofday() function calls.
If you just use \timing from psql, and run the script without explain
analyze, what speeds do you get on each?
17480ms (8.2.6)
20342ms (8.3RC2)
--
Vlad
---(end of broadcast
for partitioning.
regards, tom lane
-- vlad
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ops. alter table seems to be propagating OK in 8.2...
On 1/4/07, Vlad [EMAIL PROTECTED] wrote:
Speaking of partitioning, I see there some improvements planed for
this feature in 8.3 - any info on what exactly users can expect? Any
possibility to improve it so we don't have to add insert trigger
?
--
Vlad
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
16.002253.5100
3096
06019.595119.7916.002463.8450
3290
--
Vlad
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
-- Vlad
that subdirectory with a symlink to a (suitably
secure) directory elsewhere.
-- Vlad
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
(in fact, I think there is code somewhere
that actually runs around and cleans out the temp directories during a
restart).
-- vlad
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
and the data is stored. Then, when the index was
completed, it moved index file to array for indexes.
If my observation is accurate, and it really works this way, then it's
definitely point for improvement imho...
--
Vlad
---(end of broadcast)---
TIP 4
Good day!
I have found library libpq.lib for visual c++ (package PostgreSQL
8), but for borland builder give me error for using this library.
Where I can find libpq.lib for borland builder (win2000)?
thanks.
---(end of broadcast)---
TIP
of FreeBSD 5.4 or FreeBSD 5.4 x86_64Bit.
My question is: Will PostgreSQL 8.04 run perfectly on a 64-bit OS, or should
I run the 32 bit OS for now?
Any help will be greatly appreciated
Thanks
Craig
--
Vlad
---(end of broadcast)---
TIP 9: In versions
for that is increasing shared buffers.
later today I will do some performance testing with shared buffers set
to 50k as Tom suggested and then with, lets say 200k and post the
results here.
--
Vlad
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ
# min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 28192 # min 100, ~50 bytes each
and there is no limits on pgsql user.
any help / ideas will be appreciated
--
Vlad
---(end of broadcast)---
TIP 6: explain analyze is your
for past discussions of this topic.
ok, thnx
--
Vlad
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
OF one.t;
--
Vlad
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
yes, we actually use table alias as a workaround, I thought that it's
actually looks like error in postgresql parser (or deeper) that needs
to be reported.
thanks.
On 9/6/05, Matt Miller [EMAIL PROTECTED] wrote:
On Tue, 2005-09-06 at 13:45 -0400, Vlad wrote:
SELECT * FROM one.aa, two.bb WHERE
.
Perhaps this isn't adequately documented...
--
Vlad
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
IMHO cron would be the best for that...
is it possible to schedule the execution of an sql stored procedure in
postgress on linux?
--
Vlad
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http
statement.
speaking of visual tools - does anyone know a tool to pretty SQL
statement (ala tidy)? Sometimes we have to dig thruogh
auto-generated SQL queries 30k-60k size, which is quite hard to do
when it's one row w/o any indents and line breaks.
--
Vlad
---(end
/pgcluster )
I found that PgCluster supports multi-muster mode, which we can
benefit from, but it's not required for backup which is #1 goal at
the moment.
If anyone used those solutions, compared performance, reliability, etc
- please share your experience / thoughts.
--
Vlad
, there are
useful solutions in that space.
richard
--
Richard Welty [EMAIL PROTECTED]
Averill Park Networking
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
F=ma : it's not just a good idea, it's the law
--
Vlad
at all?
Besides, why don't you recommend turning pg_server_prepare off?
On 5/2/05, Greg Sabino Mullane [EMAIL PROTECTED] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Which is why Vlad should use prepare() instead of prepare_cached().
in our web application similar SQL queries
Regards,
Alexandre
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
--
Vlad
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http
oops... you were writing about signal 10 not signal 11. my bad - sorry
On 5/3/05, Vlad [EMAIL PROTECTED] wrote:
Alexandre,
I saw reports (and observed the problem myself) that all sort of
different softwares suffering from signal 11 under FreeBSD (more often
seen on 5-STABLE). So far
?
If there is no such function (and I can't find it), then it will be
hard for a driver to make things working right with server-side
prepared queries!
On 5/2/05, Tom Lane [EMAIL PROTECTED] wrote:
Vlad [EMAIL PROTECTED] writes:
so is it possible that a successfully prepared (and possibly a couple
of times already
and
postgtres invalidated prepares because of that.
--
Vlad
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
The docs for prepare_cached() are littered with Don't do this unless
you understand the implications warnings, as well as some kludges to
differentiate different cases.
Which is why Vlad should use prepare() instead of prepare_cached().
in our web application similar SQL queries (like
::Pg 1.32 with no server-side prepare support.
p.s. I'm not cross posting both to pgsql-general and dbd-pg lists,
cause not sure from which side dbd::pg or postgrtesql a possible
workaround can be found from.
--
Vlad
---(end of broadcast)---
TIP 6
the
upgrade. Rather I'm interested in list's opinion on possible
workaround. Though thanks for your point anyway.
--
Vlad
---(end of broadcast)---
TIP 8: explain analyze is your friend
--
Vlad
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
to
invalidate the query cache. (Not that the server side is presently
all that good about it, but at least the server side is fixable
in principle ;-))
regards, tom lane
--
Vlad
---(end of broadcast)---
TIP 1
as test;
test
--
(1 row)
test = select case when 0=0 then null else 1/0 end as test;
test
--
--
Vlad
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
90 matches
Mail list logo
