[GENERAL] While only running pg_dump, postgresql performs writes inside base/ directory. How can we stop this?
We are dumping a 4TB postgres database using pg_dump. The source is on a copy-on-write snapshot filesystem, and the target is an external drive. While the pg_dump is running we get about 4-5MB/sec writes on the filesystem where postgresql data is (iostat), and specifically inside the base/ directory. Since the only query running on this DB is the COPY initiated from pg_dump which only reads data, who is writing data to postgres datafiles? We need to find a way to make postgres stop writing because the source copy-on-write filesystem gets filled up before the dump is finished. Postgresql version is 8.4, vacuum is not running (autovacuum = off), OS is debian 6.
Re: [GENERAL] Use environment variables in postgresql.conf
Hi, On Sat, Nov 30, 2013 at 08:43:53PM -0500, imagene...@gmail.com wrote: The questions are: 1. Has var expansion in configuration files been contemplated? 2. Why not do it? Several years ago on the -hackers list (I couldn't say how many, but I was still supervising the database operations at Afilias so it's at least pre-2006) there was a big fight about environment variables and how to handle them. Feelings ran high, and I seem to recall that one of the participants involved basically pulled back from Postgres work after that because of his frustration. I'd suggest trolling the -hackers archives for environment variable during the period (say) from 2003 through 2005. But in answer to (2), basically the reason is that if you have multiple ways of setting a value, it is extremely hard for an operator to figure out what the setting of a running process _actually_ is. Environment variables are especially bad, because they're hard to retrieve from a running session in an interoperable way and they're impossible to inspect if the calling shell is gone. If you say, But I choose to take that risk, you forget that the project incurs support costs whenever such inconsistencies pop out. Reasons why it's perhaps useful to change the presumed workflow: 1. It's perhaps inconvenient I don't see why. If you're going to write a shell script anyway to set these things, I don't see what the big deal is. 2. Variables are a fundamental concept for configuration 3. Moving configuration to os specific scripts defies the DRY (don't repeat yourself) paradigm You can use include files against a base postgres.conf file to avoid repeating yourself. You're certainly going to have to repeat at least the setting of some variables. Proposed workflow: 1. Environment initialization, meaning the declaration of environment variables (in the sense that env -i is probably spawned in the OS specific scripts and is thus quite empty) for pg_ctl should be done in a postgresql specific shell file. Why in the world do you want to involve the shell in an already-complex configuration situation? Particularly since Postgres also runs on Windows? And that the standard Bourne shell is either no standard at all, or else sufficiently limited for interoperation that the paradigm effectively becomes write once, test everywhere? What you are really saying is, I have this idea to do something I can already do, only using a different work flow, so someone else should do the development work to support that. I guess if you want this badly enough you could propose the feature over on -hackers. I think it'd be a crazy feature, though. Thanks for your reply Andrew, however I do not necessarily wish to conform to arbitrary expectations forced by the current implementation if it is inconvenient/incomplete. All engineering is a trade-off. In this case, the program is designed with a different mechanism to achieve the same end. It is a bad idea -- because it's a dangerous foot-gun -- to implement a different way to achieve the same ends: it increases code complexity and dramatically increases the risk that one part of the code will be altered without correctly altering the other part. What is inconvenient for you incereases the reliability of Postgres, and I humbly suggest that your evaluation of convenience in this case is anyway incomplete. Best, A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming replication and WAL archives
On Mon, 25 Nov 2013, Tree wrote: TLDR: We want to be able to use streaming replication, WAL archiving, and have the ability to restore from a backup made before a failover using the WAL archive. (cutting rest of long description) So, is it possible to use a long-term WAL archive area (as the docs refer to this) using streaming replication, and accounting for failover? Thanks, -Alan Bryant -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] While only running pg_dump, postgresql performs writes inside base/ directory. How can we stop this?
On Mon, Dec 2, 2013 at 6:22 AM, Spiros Ioannou siv...@inaccess.com wrote: We are dumping a 4TB postgres database using pg_dump. The source is on a copy-on-write snapshot filesystem, and the target is an external drive. While the pg_dump is running we get about 4-5MB/sec writes on the filesystem where postgresql data is (iostat), and specifically inside the base/ directory. Since the only query running on this DB is the COPY initiated from pg_dump which only reads data, who is writing data to postgres datafiles? It is probably setting hint bits on the tuples it is visiting. We need to find a way to make postgres stop writing because the source copy-on-write filesystem gets filled up before the dump is finished. Vacuum the system before taking the snapshot. Postgresql version is 8.4, vacuum is not running (autovacuum = off), OS is debian 6. Since autovacuum is off, what are you doing about vacuuming? Cheers, Jeff
Re: [GENERAL] unnest on multi-dimensional arrays
Thanks for the explanation and examples! Zev On 11/28/2013 10:03 AM, Tom Lane wrote: David Johnston pol...@yahoo.com writes: Zev Benjamin wrote It appears that unnest, when called on a multi-dimensional array, effectively flattens the array first. For example: ... Multidimensional arrays do have shortcomings in the current implementation of which this is one. I'm not sure, though, if there is anything substantial and centralized in the docs so pertaining. It might be worth explaining that this is a consequence of the fact that Postgres treats all arrays over the same element type as being of the same data type --- that is, 1-D and 2-D arrays are not distinguished by the type system. Thus, when the polymorphic function unnest(anyarray) returns setof anyelement is applied to an integer array, it must return a series of integers; not a series of lower-dimensional arrays. There have been some discussions over whether this could be changed without a backwards-compatibility disaster, but nobody sees how. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unnest on multi-dimensional arrays
Hrm. Conceptually, I think you actually want something like:
CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
RETURNS SETOF anyarray
LANGUAGE plpgsql
AS $function$
DECLARE
s $1%type;
d int;
BEGIN
d := array_ndims($1) - 1;
FOREACH s SLICE d IN ARRAY $1 LOOP
RETURN NEXT s;
END LOOP;
RETURN;
END;
$function$;
Otherwise,
select * from reduce_dim(ARRAY[[1], [2], [3]])
and
select * from reduce_dim(ARRAY[[[1], [2], [3]]);
produce the same results. Unfortunately, it looks like the SLICE
keyword only accepts a constant.
Zev
On 11/28/2013 02:28 AM, Pavel Stehule wrote:
Hello
postgres=# CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
RETURNS SETOF anyarray
LANGUAGE plpgsql
AS $function$
DECLARE s $1%type;
BEGIN
FOREACH s SLICE 1 IN ARRAY $1 LOOP
RETURN NEXT s;
END LOOP;
RETURN;
END;
$function$;
CREATE FUNCTION
postgres=# select reduce_dim(array[array[1, 2], array[2, 3]]);
reduce_dim
{1,2}
{2,3}
(2 rows)
Regards
Pavel Stehule
2013/11/28 Zev Benjamin zev-pg...@strangersgate.com
mailto:zev-pg...@strangersgate.com
It appears that unnest, when called on a multi-dimensional array,
effectively flattens the array first. For example:
= select * from unnest(array[array[1, 2], array[2, 3]]);
unnest
1
2
2
3
(4 rows)
while I would have expect something like the following:
= select * from unnest(array[array[1, 2], array[2, 3]]);
unnest
{1, 2}
{2, 3}
(2 rows)
Is there any way to get the latter behavior?
Zev
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
mailto:pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/__mailpref/pgsql-general
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unnest on multi-dimensional arrays
2013/12/2 Zev Benjamin zev-pg...@strangersgate.com
Hrm. Conceptually, I think you actually want something like:
CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
RETURNS SETOF anyarray
LANGUAGE plpgsql
AS $function$
DECLARE
s $1%type;
d int;
BEGIN
d := array_ndims($1) - 1;
FOREACH s SLICE d IN ARRAY $1 LOOP
RETURN NEXT s;
END LOOP;
RETURN;
END;
$function$;
Otherwise,
select * from reduce_dim(ARRAY[[1], [2], [3]])
and
select * from reduce_dim(ARRAY[[[1], [2], [3]]);
produce the same results. Unfortunately, it looks like the SLICE keyword
only accepts a constant.
yes, it accept only constant - it is unpleasant, but it is necessary due
plpgsql internals :(
Regards
Pavel Stěhule
Zev
On 11/28/2013 02:28 AM, Pavel Stehule wrote:
Hello
postgres=# CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
RETURNS SETOF anyarray
LANGUAGE plpgsql
AS $function$
DECLARE s $1%type;
BEGIN
FOREACH s SLICE 1 IN ARRAY $1 LOOP
RETURN NEXT s;
END LOOP;
RETURN;
END;
$function$;
CREATE FUNCTION
postgres=# select reduce_dim(array[array[1, 2], array[2, 3]]);
reduce_dim
{1,2}
{2,3}
(2 rows)
Regards
Pavel Stehule
2013/11/28 Zev Benjamin zev-pg...@strangersgate.com
mailto:zev-pg...@strangersgate.com
It appears that unnest, when called on a multi-dimensional array,
effectively flattens the array first. For example:
= select * from unnest(array[array[1, 2], array[2, 3]]);
unnest
1
2
2
3
(4 rows)
while I would have expect something like the following:
= select * from unnest(array[array[1, 2], array[2, 3]]);
unnest
{1, 2}
{2, 3}
(2 rows)
Is there any way to get the latter behavior?
Zev
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
mailto:pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/__mailpref/pgsql-general
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unnest on multi-dimensional arrays
This actually looks to mostly be a parser limitation:
foreach_slice :
{
$$ = 0;
}
| K_SLICE ICONST
{
$$ = $2;
}
;
Everything after that just treats the slice number as a variable. Is
there any underlying grammar ambiguity that prevents it from being an
expression?
Zev
On 12/02/2013 01:24 PM, Pavel Stehule wrote:
2013/12/2 Zev Benjamin zev-pg...@strangersgate.com
mailto:zev-pg...@strangersgate.com
Hrm. Conceptually, I think you actually want something like:
CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
RETURNS SETOF anyarray
LANGUAGE plpgsql
AS $function$
DECLARE
s $1%type;
d int;
BEGIN
d := array_ndims($1) - 1;
FOREACH s SLICE d IN ARRAY $1 LOOP
RETURN NEXT s;
END LOOP;
RETURN;
END;
$function$;
Otherwise,
select * from reduce_dim(ARRAY[[1], [2], [3]])
and
select * from reduce_dim(ARRAY[[[1], [2], [3]]);
produce the same results. Unfortunately, it looks like the SLICE
keyword only accepts a constant.
yes, it accept only constant - it is unpleasant, but it is necessary due
plpgsql internals :(
Regards
Pavel Stěhule
Zev
On 11/28/2013 02:28 AM, Pavel Stehule wrote:
Hello
postgres=# CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
RETURNS SETOF anyarray
LANGUAGE plpgsql
AS $function$
DECLARE s $1%type;
BEGIN
FOREACH s SLICE 1 IN ARRAY $1 LOOP
RETURN NEXT s;
END LOOP;
RETURN;
END;
$function$;
CREATE FUNCTION
postgres=# select reduce_dim(array[array[1, 2], array[2, 3]]);
reduce_dim
{1,2}
{2,3}
(2 rows)
Regards
Pavel Stehule
2013/11/28 Zev Benjamin zev-pg...@strangersgate.com
mailto:zev-pg...@strangersgate.com
mailto:zev-pgsql@__strangersgate.com
mailto:zev-pg...@strangersgate.com
It appears that unnest, when called on a multi-dimensional
array,
effectively flattens the array first. For example:
= select * from unnest(array[array[1, 2], array[2, 3]]);
unnest
1
2
2
3
(4 rows)
while I would have expect something like the following:
= select * from unnest(array[array[1, 2], array[2, 3]]);
unnest
{1, 2}
{2, 3}
(2 rows)
Is there any way to get the latter behavior?
Zev
--
Sent via pgsql-general mailing list
(pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org
mailto:pgsql-general@__postgresql.org
mailto:pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
http://www.postgresql.org/__mailpref/pgsql-general
http://www.postgresql.org/__mailpref/pgsql-general
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
mailto:pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/__mailpref/pgsql-general
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unnest on multi-dimensional arrays
Zev Benjamin zev-pg...@strangersgate.com writes: This actually looks to mostly be a parser limitation: Well, you'd also need some execution-time infrastructure to evaluate an expression, if we allowed one there, but I agree it wouldn't be a tremendously complicated patch. We'd just not foreseen a good reason to support an expression ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help ! FAILOVER with Slony-I.
Hi, everyone. I'm using Slony-I and Streaming Replication. how to failover with slony-i ? I have an example FAILOVER ( ID = 1, BACKUP NODE = 2 ); but how to use it ? pl, help me ! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Help-FAILOVER-with-Slony-I-tp5781156.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help ! FAILOVER with Slony-I.
On 12/2/2013 4:11 AM, Tobadao wrote: I'm using Slony-I and Streaming Replication. those are two completely different and incompatible replication methods. a streaming replication MASTER could participate in slony table replication with a 3rd server, but afaik a streaming SLAVE can't/shouldn't run slony at all. Slony probably has its own mailing lists which likely would be more suitable for answering your other questions. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] NpgsqlCopySerializer blows up if no rows are saved
I am not sure if this is the correct place to post my question to. If this is
a wrong list, can you tell me the right one.
I am bulk loading rows using the following code:
const string connString =
myConnString;
using(var conn = new NpgsqlConnection(connString))
{
conn.Open();
var command = conn.CreateCommand();
var copyStr = string.Format(COPY staging.test(col1,col2) FROM STDIN);
command.CommandText = copyStr;
command.CommandType = CommandType.Text;
var serializer = new NpgsqlCopySerializer(conn);
var copyIn = new NpgsqlCopyIn(command, conn, serializer.ToStream);
copyIn.Start();
foreach (var row in incomingRows)
{
//feed the values here
}
copyIn.End();
serializer.Flush();
serializer.Close();
command.Dispose();
}
If incomingRows has one or more items, this works. However, if no rows are
saved, serializer.Close() blows up:
System.NullReferenceException : Object reference not set to an instance of
an object.
at Npgsql.NpgsqlCopySerializer.Close()
What am I doing wrong?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/NpgsqlCopySerializer-blows-up-if-no-rows-are-saved-tp5781300.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Mismatched pg_class.reltuples between table and primary key
We recently experienced a hard crash of a dev server due to lack of
resources (we think, still investigating).
That resulted in an interesting scenario where one of the tables was
returning 12 rows from select * from tbl, but select * from tbl order by
pk column was only returning 11.
Looking at pg_class.reltuples, it could be clearly seen that the table in
question had a value of 12, but the primary key had only 11.
My system catalog ignorance is probably showing, but I assumed that the
table and PK pg_class.reltuples values should always be the same?
The initial observations were made by a developer:
1). After an ANALYZE, PK reltuples remained at 11.
2). After REINDEX, PK reltuples was still 11.
3). After VACUUM FULL, PK reltuples was reset to 12.
The db was quiescent at this point (rows were not being added or removed).
I would chalk this up to a localized index corruption issue, but earlier
today I observed the same thing on a different table which did not
originally show a difference between the table and PK reltuples values.
This is following a VACUUM FREEZE, and an ANALYZE run, as well as at least
two restarts of the db cluster since the initial failure.
Can anyone suggest whether this is an index corruption issue, or maybe a
misunderstanding on my part about what the reltuples attribute means? Or
perhaps it is something else?
PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-54), 64-bit
default_statistics_target = 200
Here is a query I hacked together to raise a WARNING if the reltuples value
of a table was different than the associated primary key or unique key. I
visually ignored any values that were above the default_statistics_target
value, to factor out sample size issues.
DO
$$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT n.nspname schemaname,
c.relname as tablename,
c.reltuples::NUMERIC as tbl_tuples,
i.relname as idx_name,
i.reltuples::NUMERIC as idx_tuples
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'::char
AND i.relkind = 'i'::char
AND n.nspname NOT IN ('pg_catalog','information_schema')
AND (x.indisunique IS TRUE OR x.indisprimary IS TRUE)
ORDER BY 1,2,4
LOOP
IF ( rec.idx_name IS NOT NULL
AND rec.tbl_tuples IS DISTINCT FROM rec.idx_tuples ) THEN
RAISE WARNING 'Mismatched tuple counts - Table %, tuples: %,
unique key: %, tuples: %',
rec.schemaname||'.'||rec.tablename, rec.tbl_tuples,
rec.idx_name, rec.idx_tuples;
END IF;
END LOOP;
END
$$;
[GENERAL] PgAgent Error 1053 on Windows 2008 R2 Server
Dear Sir/Madam, PgAgent is failing to start, giving an Error 1053: The service did not respond to the start or control request in a timely fashion on a Windows 2008 R2 server. I can run pgagent no problems in DEBUG mode. I have un-installed and re-installed PgAgent multiple times. I have checked the service entry in the Registry and compared it with other windows servers I have it running successfully. If some someone could point me down the right path as to why the service is not starting it would be appreciated. Regards, Mark Street Support Instinct Systems
Re: [GENERAL] NpgsqlCopySerializer blows up if no rows are saved
Hi!
You should post Npgsql questions to our user's forums :
http://forums.npgsql.org
In this case I think it is a bug in Npgsql. Please add this test program to
your post when talking about this problem.
Thanks in advance.
Em 02/12/2013 19:29, AK alk...@gmail.com escreveu:
I am not sure if this is the correct place to post my question to. If this
is
a wrong list, can you tell me the right one.
I am bulk loading rows using the following code:
const string connString =
myConnString;
using(var conn = new NpgsqlConnection(connString))
{
conn.Open();
var command = conn.CreateCommand();
var copyStr = string.Format(COPY staging.test(col1,col2) FROM STDIN);
command.CommandText = copyStr;
command.CommandType = CommandType.Text;
var serializer = new NpgsqlCopySerializer(conn);
var copyIn = new NpgsqlCopyIn(command, conn, serializer.ToStream);
copyIn.Start();
foreach (var row in incomingRows)
{
//feed the values here
}
copyIn.End();
serializer.Flush();
serializer.Close();
command.Dispose();
}
If incomingRows has one or more items, this works. However, if no rows are
saved, serializer.Close() blows up:
System.NullReferenceException : Object reference not set to an instance of
an object.
at Npgsql.NpgsqlCopySerializer.Close()
What am I doing wrong?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/NpgsqlCopySerializer-blows-up-if-no-rows-are-saved-tp5781300.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NpgsqlCopySerializer blows up if no rows are saved
I did a week ago - and got no replies at all. Thank you. -- View this message in context: http://postgresql.1045698.n5.nabble.com/NpgsqlCopySerializer-blows-up-if-no-rows-are-saved-tp5781300p5781316.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] query creates a huge toast tables
On Thu, Nov 28, 2013 at 8:25 AM, steve.tout...@inspq.qc.ca wrote: Hi, This query (postgis) didn't endup after 4 hoursand eat 40gig of disk space SELECT id, gridcode, ST_Difference(a.geom32198_s,b.geom32198_s) as geom32198_s into potentialite FROM province as a, potentialite_tmp as b; I stopped the execution and cancel request sent appears, but after an hour the request wasn't stopped yet. So I killed the process and restat postgres. The database is up and running but I didn't get the 40gig of disk space back. And only 5gig remains on the server It is this table that is so huge pg_toast.pg_toast_11037520 What table (if any) does it belong to? select relname from pg_class where oid=11037520 Cheers, Jeff
[GENERAL] Inserting boolean types as an alias?
Hello I am new to this site and also a student. I am working on an assignment and was wondering if there is a way to make an alias for a boolean? For example when I am making a table called club_games and it must contain the memberID, gameID, count, and status as column fields. When I enter the data for status I want it to say either unavailable for false and available for true. but I don't know how to do that. This is what I have currently for the line that I am specifically talking about: INSERT INTO club_Games(memberID, gameID, hardwareID, count, status) VALUES ((SELECT memberID FROM members WHERE name = 'Fred Flinstone'), (SELECT gameID FROM games WHERE name = 'Jurrasic Park'), NULL, 1, 'true'); when I do the SELECT * FROM club_Games this is my output: id gameidmemberid hardwareidcount status 1 12345z A12345 null 1 t Where it says t for status I wanted it to say available but mean true for boolean, but I don't know how to do that. Thanks to all for help. My file is attached if you would like to view my assignment. task5.txt http://postgresql.1045698.n5.nabble.com/file/n5781328/task5.txt -- View this message in context: http://postgresql.1045698.n5.nabble.com/Inserting-boolean-types-as-an-alias-tp5781328.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Does --no-unlogged-table-data prevent table locking?
If a database has an unlogged table and that table is truncated during while a database dump with the --no-unlogged-table-data option is running, will the table be locked against a truncate or will the truncate succeed? The documentation is unclear on this detail. Background: We have a regular Bash script that essentially does the following: 1. Some preprocessing 2. psql: ...empty a table, populate the table, do some processing on the table... 3. More non PG steps in Bash 4. psql ...some remaining processing on the table and export the results... 5. Cleanup We are now colliding with a nightly dump which is delaying this script. Note that since there is a break between the two calls to psql, we cannot use a temporary table but an unlogged table is perfectly safe for our purposes. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inserting boolean types as an alias?
On 12/02/2013 03:46 PM, Nick wrote: Hello I am new to this site and also a student. I am working on an assignment and was wondering if there is a way to make an alias for a boolean? For example when I am making a table called club_games and it must contain the memberID, gameID, count, and status as column fields. When I enter the data for status I want it to say either unavailable for false and available for true. but I don't know how to do that. This is what I have currently for the line that I am specifically talking about: INSERT INTO club_Games(memberID, gameID, hardwareID, count, status) VALUES ((SELECT memberID FROM members WHERE name = 'Fred Flinstone'), (SELECT gameID FROM games WHERE name = 'Jurrasic Park'), NULL, 1, 'true'); when I do the SELECT * FROM club_Games this is my output: id gameidmemberid hardwareidcount status 1 12345z A12345 null 1 t Where it says t for status I wanted it to say available but mean true for boolean, but I don't know how to do that. Thanks to all for help. If the field has to be a boolean, then alias it on the select: select case when status = 't' then 'available' else 'unavailable' end from club_Games otherwise make status a text field and just enter 'available' or 'unavailable' -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inserting boolean types as an alias?
On 12/2/2013 3:46 PM, Nick wrote: Hello I am new to this site and also a student. I am working on an assignment and was wondering if there is a way to make an alias for a boolean? For example when I am making a table called club_games and it must contain the memberID, gameID, count, and status as column fields. When I enter the data for status I want it to say either unavailable for false and available for true. but I don't know how to do that. you could use an enum, or you could use a case statement on your query, like, SELECT memberID, gameID, hardwareID, count, case when status then 'available' else 'unavailable' end FROM club_Games -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Mismatched pg_class.reltuples between table and primary key
bricklen brick...@gmail.com writes: We recently experienced a hard crash of a dev server due to lack of resources (we think, still investigating). That resulted in an interesting scenario where one of the tables was returning 12 rows from select * from tbl, but select * from tbl order by pk column was only returning 11. Looking at pg_class.reltuples, it could be clearly seen that the table in question had a value of 12, but the primary key had only 11. My system catalog ignorance is probably showing, but I assumed that the table and PK pg_class.reltuples values should always be the same? I wouldn't assume that --- they're only approximations, anyway. In particular there's some moving-average behavior in there that might prevent small errors in the value from ever going away completely. The initial observations were made by a developer: 1). After an ANALYZE, PK reltuples remained at 11. 2). After REINDEX, PK reltuples was still 11. 3). After VACUUM FULL, PK reltuples was reset to 12. (2) is a bit surprising, but I'm not sure that REINDEX recomputes the reltuples value for the index. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Mismatched pg_class.reltuples between table and primary key
bricklen escribió: We recently experienced a hard crash of a dev server due to lack of resources (we think, still investigating). That resulted in an interesting scenario where one of the tables was returning 12 rows from select * from tbl, but select * from tbl order by pk column was only returning 11. Looking at pg_class.reltuples, it could be clearly seen that the table in question had a value of 12, but the primary key had only 11. This sounds very similar to what was reported in http://www.postgresql.org/message-id/20131126123244.gi23...@alap2.anarazel.de In short, you probably want to make sure to upgrade to this week's release. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Mismatched pg_class.reltuples between table and primary key
Hi Alvaro, Thanks for the link. On Mon, Dec 2, 2013 at 4:35 PM, Alvaro Herrera alvhe...@2ndquadrant.comwrote: bricklen escribió: We recently experienced a hard crash of a dev server due to lack of resources (we think, still investigating). That resulted in an interesting scenario where one of the tables was returning 12 rows from select * from tbl, but select * from tbl order by pk column was only returning 11. Looking at pg_class.reltuples, it could be clearly seen that the table in question had a value of 12, but the primary key had only 11. This sounds very similar to what was reported in http://www.postgresql.org/message-id/20131126123244.gi23...@alap2.anarazel.de In short, you probably want to make sure to upgrade to this week's release. If it is indeed the same issue (and I see something similar in an actual production system running 9.2.5 which was recovered from a crashed LVM just the other day), are the chances of data corruption particularly high? From reading over that linked thread, it looked like the problem was likely to stem from wraparound. Once the patch is applied, will the existing data still be vulnerable? I haven't been paying close attention to that particular thread, so I might have missed a few things regarding the fixes -- will a full dump + reload be necessary? Thanks, Bricklen
Re: [GENERAL] Inserting boolean types as an alias?
John R Pierce wrote On 12/2/2013 3:46 PM, Nick wrote: Hello I am new to this site and also a student. I am working on an assignment and was wondering if there is a way to make an alias for a boolean? For example when I am making a table called club_games and it must contain the memberID, gameID, count, and status as column fields. When I enter the data for status I want it to say either unavailable for false and available for true. but I don't know how to do that. you could use an enum, or you could use a case statement on your query, like, SELECT memberID, gameID, hardwareID, count, case when status then 'available' else 'unavailable' end FROM club_Games Not sure an enum would work since the source table has the status column defined as boolean... Condition conversion requires the use of the CASE expression. One point not made is that the column allows for NULL. I like to avoid 3-value logic thinking when possible so I'd suggest: CASE WHEN status IS NULL THEN 'Unknown' WHEN status = true THEN 'Available' ELSE 'Unavailable' END::varchar AS status_description Then, if you need the logic elsewhere put that into a SQL function so you can change the words without having to change every location the conversion may be used. You may also want a description_to_status function to perform the inverse. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Inserting-boolean-types-as-an-alias-tp5781328p5781337.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inserting boolean types as an alias?
On 12/2/2013 5:18 PM, David Johnston wrote: Not sure an enum would work since the source table has the status column defined as boolean... well, you'd need to alter the table to use an enum, of course. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NpgsqlCopySerializer blows up if no rows are saved
Whooops. Sorry for not replying. :-( I'll check the problem and reply you back. Em 02/12/2013 20:21, AK alk...@gmail.com escreveu: I did a week ago - and got no replies at all. Thank you. -- View this message in context: http://postgresql.1045698.n5.nabble.com/NpgsqlCopySerializer-blows-up-if-no-rows-are-saved-tp5781300p5781316.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
