Re: [GENERAL] Basic Question on Point In Time Recovery
> The thing is you can use desktop class machines for the slave. If you do > not have spare machines I would suggest a desktop class machine with big > RAM and whatever disks you need for the DB plus an extra disk to pg_dump to > ( so pg_dump does not compete with DB for the db disks, this really kills > performance ). Replication slaves do not need that much RAM ( as the only > query it is going to run is the pg_dump ones, but desktop ram is cheap ). > We did this with a not so powerful desktop with an extra sata disk to store > the pg_dumps and it worked really well, and we are presently using two > servers, using one of the extra gigabit interfaces with a crossover cable > for the replication connection plus an extra sata disk to make hourly > pg_dumps and it works quite well. If load on the backup server becomes an issue you might be able to make incremental pg_dump's onto tmpfs. Advantage there is that the dump iteslf has effectively no write I/O overhead: you can dump to tmpfs and then [bg]zip to stable storage w/o beating up the disks, which becomes a real problem with comodity-grade hardware. -- Steven Lembark 3646 Flora Pl Workhorse Computing St Louis, MO 63110 lemb...@wrkhors.com +1 888 359 3508 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Effecient time ranges in 9.4/9.5?
Trying to store open hours for storefront operations. These are degenerate sets of ( store + weekday + open time + close time ) (i.e., candidate key == all fields). Ultimate goal is to compare hours for service times (e.g., seating, pickup, delivery) to food prep times (e.g., breakast or lunch menu). I'd like to store them as: ( store + weekday + timerange ) to simplify exclusion constraints and joins for overlapping food prep and service times. Lacking a built-in "timetzrange", I'm stuck defining the type. I think a working subtype_diff to effeciently support exclusion constraints on ( store with =, + weekday with =, hours with &&). In particular, a working subtype_diff, assuming that the hours are all in the range of .. 2400 (i.e., no cross-day intervals). The examples in [1] & [2] don't include a working subtype_diff (just a reference to "float8mi" without defining it). At the least a working time -> float8 operator might be nothing more than a cast but I don't see how to do it offhand. There are several cases I've found of people wanting to create a working time range, without any specifics of how (e.g., [3]). I can see where the built-in would have issues ([4], [5]) but using time ranges with dates as templates to produce timestamp-ranges makes life s much easier with scheduling. The 9.4 doc's describe the subtype_diff as necessary for effective gist indexing. Then again, the builtins for time may be sufficient to just define subtype = timetz and be done with it... I cannot find any references either way. It's not that hard to handle differences mod-24hrs: diff = ( upper - lower + 24 % 24 ); if upper < lower the +24 corrects the sign; if upper > lower the % 24 keeps the result in range. I'm just not entirely Q: Is the subtype_diff really useful for indexing if the subtype is timetz? Q: If so, where is an example to an effecient diff for the times? thanks [1] <http://www.postgresql.org/docs/9.4/static/sql-createtype.html> [2] <http://www.postgresql.org/docs/9.4/static/rangetypes.html> [3] <http://stackoverflow.com/questions/28017891/postgres-custom-range-type> [4] <http://grokbase.com/t/postgresql/pgsql-general/128355kvhc/range-types-in-9-2> [5] <https://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL> -- Steven Lembark 3646 Flora Pl Workhorse Computing St Louis, MO 63110 lemb...@wrkhors.com+1 888 359 3508 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trying to create array of enum to array of text for exclusion constraint
Using Pg 9.5.2 on linux.
Trying to create an exclusion constraint on an array of enums.
Ultimate goal is having a constraint that excludes records with
overlapping elements.
This must have been done before, I just cannot find any examples.
I realize there isn't a q&d way to convert enums to integers
(e.g.,
<http://stackoverflow.com/questions/12344213/postgresql-is-it-possible-cast-enum-to-integer#12347716>)
but there should be a way to convert enums to text for this purpose.
For example, with a scalar enum this works:
e.g.,
drop type if exists week_day cascade;
create type week_day as
enum
(
'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'
);
/*
* works for exclude using gist as "week_day_text( X ) with =".
*/
create or replace function week_day_text
(
week_day
)
returns text
language sql strict immutable as
$$
select $1::text;
$$
;
/*
* this works if days is week_day w/o array and
* the exclusion uses week_day_text( day ).
*/
drop table if exists timeslot cascade;
create table timeslot
(
/*
* this would normally also have hours,
* for this example weekday is sufficient.
*/
day week_day not null,
exclude using gist
(
week_day_text( day ) with =
)
);
Goal is replacing day with an array of week_day as:
day week_day[] not null,
Using "day with &&" leaves me with (whitespace added):
drop table if exists timeslot cascade;
create table timeslot
(
/*
* this would normally also have hours,
* for this example weekday is sufficient.
*/
day week_day[] not null, /* add array of enum */
exclude using gist
(
day with &&
)
);
psql:hak:43: ERROR: data type week_day[] has no
default operator class for access method "gist"
HINT: You must specify an operator class for
the index or define a default operator class for
the data type.
Using the text function blows up because it doesn't support arrays
(again, whitespace added for readability):
(
...
exclude using gist
(
week_day_text( day ) with &&
)
);
psql:hak:43: ERROR: function week_day_text(week_day[]) does not exist
LINE 10: week_day_text( day ) with &&
^
HINT: No function matches the given name and
argument types. You might need to add explicit type casts.
Using array_to_string won't be sufficient since that would allow
overlaps due to different orders of array elements.
So... what I think I need is a plsql function that takes an
array of weekday and retuns an array of text?
/*
* convert array of week_day enum values to array of
* text for exclusion constraints.
*/
create or replace function week_day_array_text
(
week_day[]
)
returns text[]
language sql strict immutable as
$$
/*
* what is the syntax for generating this array?
* effectively I need a "map { $1::text }" in plsql.
*/
$$
;
or is there something built in that I have missed?
Note: Performance will not be an issue here as the table is not
updated all that frequently.
Any references appreciated.
--
Steven Lembark 3646 Flora Pl
Workhorse Computing St Louis, MO 63110
lemb...@wrkhors.com+1 888 359 3508
--
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] Very slow update / hash join
> > It's kind of annoying that I would need to drop the indexes that > > aren't modified just to run an update query. > > I dropped all the index except for the primary key. It was still > as slow when it started, but then I forced the primary key into > the filesystem cache and it seems to be much happier now, average > reading at about 10 MB/s, writing at 30 MB/s. Look at the PG tuning doc's. It seems as if you have too little index cache assigned for the size of your database (and its indexes). Dropping indexes isn't the real answer, tuning the database to accomodate them is a better bet. It would also be worth checking whether the I/O was entirely due to sequential reads or may have been swapping. procinfo, vmstat, or just top can tell you about that. -- Steven Lembark 3646 Flora Pl Workhorse Computing St Louis, MO 63110 lemb...@wrkhors.com +1 888 359 3508 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Wither 8.3 doc's on cast operator for domain data types?
Using Postgres 8.3.6, trying to run a query with
bound parameters gives me:
ERROR: XX000: error from Perl function
"expire_facts": operator does not exist: retire
d_date => retired_date at line 56.
"retired_date" is one of a set of domains defined via:
create domain publish_date
timestamp
not null
/* no default */
;
create domain retired_date
timestamp
not null
default 'infinity'
;
create domain insert_date
timestamp
not null
default now()
;
create domain expired_date
timestamp
not null
default 'infinity'
;
At one point I have an insertion trigger that
rejectes duplicate records. It uses a prepared
query via plperl:
spi_prepare
q
{
select
facts_id,
publish,
compare,
value,
large
from
facts
where
identifier_id = $1
and
type_id = $2
and
ontology_id = $3
and
sub_type_id = $4
and
unit_id = $5
and
publish <= $6 /* where the problem lies */
and
retired => $6 /* where the problem lies */
},
(
( 'INTEGER' ) x 5, # $required_fieldz, $default_fieldz
'TIMESTAMP' # $pub
);
I have tried various forms of "cast( $6 as retired_date )",
"$6::retired_date", but skill get the XX000 error.
Google and searching the 8.3 online doc's havn't gotten
me any doc's or examples on syntax for defining the
missing conversion operator.
Q: Where in the 8.3 doc's are the converson operators
(or a cast syntax for bound parameters based on
domains) described?
Or is the problem that I cannot re-cycle $6 when
it has to be cast to two separate domains -- even
if both of them are based on "timestamp"?
Example log entry using "cast( $6 as retired_date ):
LOG: 0: duration: 0.340 ms bind dbdpg_p24876_1: insert into facts
(compare,value,s
ub_type_id,dataset_id,identifier_id,unit_id,publish,type_id,ontology_id) values
($1,$2,$3,$4,$5,$6,$7,$8,$9) 2009-06-11 11:28:29.420 EDT lembark 4a31229d.6132
0 3/37 10 DETAIL: parameters: $1 =
'010001101110110001100010111010100110010011101001011010101
110101101100100101001001100', $2 = '{"bacterium P2"}', $3 = '16', $4 =
'3', $5 = '41762', $6 = '0', $7 = '2009-06-10 16:20:18', $8 = '13', $ 9 = '3'
LOCATION: exec_bind_message, postgres.c:1784
INFO: 0: ARRAY(0xd3db18)
LOCATION: do_spi_elog, SPI.xs:27
STATEMENT: insert into facts
(compare,value,sub_type_id,dataset_id,identifier_id,unit
_id,publish,type_id,ontology_id) values ($1,$2,$3,$4,$5,$6,$7,$8,$9)
ERROR: XX000: error from Perl function "expire_facts": operator does not
exist: retired_date => retired_date at line 56.
LOCATION: plperl_call_perl_trigger_func, plperl.c:1179
This seems odd in particular since
operator does not exist: retired_date => retired_date at line 56.
seems to indicate that retired date cannot be
cast to itself?
This also seems odd since passing in timestamps
with other queries seems to work happily and
perform the convrsion automatically.
thanx
--
Steven Lembark85-09 90th St.
Workhorse Computing Woodhaven, NY, 11421
lemb...@wrkhors.com +1 888 359 3508
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question on round-robin partitioning
Purely for performance, I was looking into partitioning
some tables round-robin by value. Question is whether
there is any way to make use of this in constraint
exclusion.
Say I have a table foo with serial variable "foo_id".
The partition checks are
foo_id % 8 = 0
foo_id % 8 = 1
foo_id % 8 = 2
...
If I query on foo_id % 8, explain shows the optimizer
using the constraint (1).
If I just query on foo_id = 100, the exclusion is
not used (2).
What would be the best way to feed the optimizer
enough data to use the partitioning with equality
queries?
I've come up with adding a field in the various
tables to store the id % 8 values and adding
"and a.mod8_field = b.mod8_field" but hopefully
there is a better way.
That or it might be a useful addition to some
later version to handle "serial field % N" in
the optimizer.
thanx
Example 1: explain with foo_id % 8
explain select * from foo where foo_id % 8 = 1;
QUERY PLAN
Result (cost=0.00..32.60 rows=4 width=164)
-> Append (cost=0.00..32.60 rows=4 width=164)
-> Seq Scan on foo (cost=0.00..16.30 rows=2 width=164)
Filter: ((foo_id % 8) = 1)
-> Seq Scan on foo_1 foo (cost=0.00..16.30 rows=2 width=164)
Filter: ((foo_id % 8) = 1)
(6 rows)
explain select * from facts where identifier_id % 8 in ( 1, 3 );
QUERY PLAN
Result (cost=0.00..48.90 rows=12 width=164)
-> Append (cost=0.00..48.90 rows=12 width=164)
-> Seq Scan on facts (cost=0.00..16.30 rows=4 width=164)
Filter: ((identifier_id % 8) = ANY ('{1,3}'::integer[]))
-> Seq Scan on facts_1 facts (cost=0.00..16.30 rows=4 width=164)
Filter: ((identifier_id % 8) = ANY ('{1,3}'::integer[]))
-> Seq Scan on facts_3 facts (cost=0.00..16.30 rows=4 width=164)
Filter: ((identifier_id % 8) = ANY ('{1,3}'::integer[]))
Example 2: explain with foo_id = 1
explain select * from foo where foo_id = 1;
QUERY PLAN
--
Result (cost=4.27..131.61 rows=18 width=164)
-> Append (cost=4.27..131.61 rows=18 width=164)
-> Bitmap Heap Scan on foo (cost=4.27..9.61 rows=2 width=164)
Recheck Cond: (foo_id = 1)
-> Bitmap Index Scan on foo_foo_id (cost=0.00..4.27 rows=2
width=0)
Index Cond: (foo_id = 1)
-> Seq Scan on foo_0 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
-> Seq Scan on foo_1 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
-> Seq Scan on foo_2 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
-> Seq Scan on foo_3 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
-> Seq Scan on foo_4 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
-> Seq Scan on foo_5 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
-> Seq Scan on foo_6 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
-> Seq Scan on foo_7 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
(22 rows)
--
Steven Lembark85-09 90th St.
Workhorse Computing Woodhaven, NY, 11421
lemb...@wrkhors.com +1 888 359 3508
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question on inserting non-ascii strings
Using Postgres 8.3 with DBI 1.607, DBD::Pg 2.12.0, perl v5.10.0, I am trying to insert the medline database contents, which include non-ascii char's, hopefully using a prepared query. Playing with the locale, encoding, client_encoding, standard_conforming_strings, leaves me able to insert values using one-off querys, but I need to find out the correct way to handle these using prepared querys (currently using C, SQL_ASCII, SQL_ASCII, off). For example, given: create table foo ( bar varchar(255) ); and the author's name "P\x8FAZEK" Setting the client_encoding to "SQL_ASCII" does not help: the values can be inserted via E'P\x8FAZEK' but E'$1' will simply insert the "$1" literal into the table. Using convert_from( $1, 'SQL_ASCII' ) gets gets the values input, but with a warning. Q: Is there any combination of locale, encoding, client_encoding or functions that will allow me to insert values with these escape sequences without getting the warnings? Trying this in psql with various combinations of prepares statements leaves me unable to use convert_from with a varchar argument (requires bytea). If there is an example in the doc's I'd appreciate a link to it. thanks -- Steven Lembark85-09 90th St. Workhorse Computing Woodhaven, NY, 11421 lemb...@wrkhors.com +1 888 359 3508 -- 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] Question on inserting non-ascii strings
On Thu, 14 May 2009 18:44:57 +0100 Sam Mason wrote: > You want to be using whatever language you're generating the parameter > from (Perl) to handle the expansion of escape sequences for you. This > will cause the expanded string (i.e. the escapes have been interpreted) > to be sent to Postgres and everything should just work. Unfortunately I > don't use Perl much, so can't give much in the way of a demo--hopefully > others will. That is what I thought should happen, but using a database with encoding of UTF8 and client_encoding of UTF8 still gave me the warnings. thanx -- Steven Lembark85-09 90th St. Workhorse Computing Woodhaven, NY, 11421 lemb...@wrkhors.com +1 888 359 3508 -- 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] Question on inserting non-ascii strings
> Which "warnings" are you talking about? I thought you said you had
> plain SQL working OK, but were struggling to pass parameters containing
> UTF-8 encoded characters.
>
> Are you sure that your Perl code is passing the string encoded as UTF8?
Excellent point: Perl will only pass through the
converted UTF8, if I remember to convert it from
unicode!
The earlier version of the database used SQL_ASCII
and took the unicode byte as-is with a nastygram
about trans-ascii byte -- which led me down the
path of E'xxx'. While trying to make that work I'd
accidentally fat-figered out the utf8 conversion
attempting to comment it out.
Character Unicode CodeUnicode Name UTF Encoding
(from
http://www.nlm.nih.gov/databases/dtd/medline_character_database.html#notes)
use utf8;
x $c = "\x{F8}"
x utf8::encode $c;
x $c
0 'ø' <-- UTF8 for a slashed o.
$sth->execute( $c )
x $d = $dbh->selectall_arrayref( 'select * from foo' );
0 ARRAY(0x18ef0d0)
0 ARRAY(0x18cc1e8)
0 'ø' <-- bytes in correct order
x utf8::decode $d->[0][0]
DB<106> x $d
0 ARRAY(0x18ef0d0)
0 ARRAY(0x18cc1e8)
I'm still not sure whether using UTF8 or unicode is
the best way going forward, but will probably stick
with UTF8 in case I have to deal with any offball
character sets.
thanx
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
