Re: [GENERAL] vacuum on streaming replication

On 31 July 2017 04:15:33 GMT+02:00, Alex Samad wrote: >Hi > >setup a cluster, with streaming replication and hot stand by > >the idea is to use the stand by to do queries whilst the primary is >doing >inserts. > >But I noticed the stats on the stand by server don't update, nor can I >run >vacuum a

Re: [GENERAL] Invalid byte sequence for encoding UTF-8 0xc3\n

John, On Sun, Jul 30, 2017 at 5:32 PM, Igor Korot wrote: > Hi, John, > > On Sun, Jul 30, 2017 at 4:53 PM, John R Pierce wrote: >> On 7/30/2017 1:43 PM, Igor Korot wrote: >> >> what encodings are default on your system ?`\l+` in psql should show the >> encodings. >> >> Is this "backslash + pi

[GENERAL] vacuum on streaming replication

Hi setup a cluster, with streaming replication and hot stand by the idea is to use the stand by to do queries whilst the primary is doing inserts. But I noticed the stats on the stand by server don't update, nor can I run vacuum against it as its in recovery mode. So how do update the stats and

Re: [GENERAL] partitioning question

How expensive is dynamic over static. I'm looking at storing yearly now, so I figure if my if then clause has the latest year at the top it should be very quick. On 31 July 2017 at 11:07, Justin Pryzby wrote: > On Mon, Jul 31, 2017 at 10:25:54AM +1000, Alex Samad wrote: > > I note that you l

Re: [GENERAL] partitioning question

On Mon, Jul 31, 2017 at 10:25:54AM +1000, Alex Samad wrote: > I note that you link to P10 and I am currently looking at 9.6. The changes > do look nice for partitioning for p10. Yes sorry, pg10 is beta - avoid using it except for testing purposes. > I will add currently we don't delete anything,

Re: [GENERAL] partitioning question

Hi I note that you link to P10 and I am currently looking at 9.6. The changes do look nice for partitioning for p10. Interesting your suggest that the MM parition isn't that bad. I will add currently we don't delete anything, we will keep adding to it. Also I am thinking my insert trigger

Re: [GENERAL] partitioning question

On Mon, Jul 31, 2017 at 09:15:29AM +1000, Alex Samad wrote: > Hi > > I was about to partition a large (?) approx 3T of data 2B rows into > partition tables but broken up into MM ... > > Now I have been reading about limiting the number of partitions otherwise > it could slow down the parser.

[GENERAL] partitioning question

Hi I was about to partition a large (?) approx 3T of data 2B rows into partition tables but broken up into MM ... Now I have been reading about limiting the number of partitions otherwise it could slow down the parser. My reasoning for limiting to MM was that most of the request would be

Re: [GENERAL] Invalid byte sequence for encoding UTF-8 0xc3\n

Hi, John, On Sun, Jul 30, 2017 at 4:53 PM, John R Pierce wrote: > On 7/30/2017 1:43 PM, Igor Korot wrote: > > what encodings are default on your system ?`\l+` in psql should show the > encodings. > > Is this "backslash + pipe + plus-sign"? > > Trying it gives: "Invalid command". > > > \ + low

[GENERAL] Fwd: Planner oversight for GIN indices?

On postgres 9.6 and 9.5 I have tested a structure like this create table test (data text); create index on test using gin(data gin_trgm_ops); insert into test select md5(num::text) from generate_series(0, 100) as A(num); analyze test; explain select * from test where data like '%a%'; explain

Re: [GENERAL] Invalid byte sequence for encoding UTF-8 0xc3\n

On 7/30/2017 1:43 PM, Igor Korot wrote: what encodings are default on your system ?`\l+` in psql should show the encodings. Is this "backslash + pipe + plus-sign"? Trying it gives: "Invalid command". \ + lower case L + plus sign, thats the psql metacommand to list all databases with ext

Re: [GENERAL] Invalid byte sequence for encoding UTF-8 0xc3\n

Hi, John, On Sun, Jul 30, 2017 at 4:34 PM, John R Pierce wrote: > On 7/30/2017 1:19 PM, Igor Korot wrote: >> >> I am using a database for my project that I created inside SQLite3. >> This database contains a table called "abc" (it is "abc" + >> symbol with the code 225 - >> greek letter "beta or

Re: [GENERAL] Invalid byte sequence for encoding UTF-8 0xc3\n

On 7/30/2017 1:19 PM, Igor Korot wrote: I am using a database for my project that I created inside SQLite3. This database contains a table called "abc" (it is "abc" + symbol with the code 225 - greek letter "beta or a German symbol for "ss"). in what encoding? in ISO 8859-1, -15, beta aka sha

[GENERAL] Invalid byte sequence for encoding UTF-8 0xc3\n

Hi, ALL, I am using a database for my project that I created inside SQLite3. This database contains a table called "abc" (it is "abc" + symbol with the code 225 - greek letter "beta or a German symbol for "ss"). All I did was to export that database into the text file and then imported this file i

Re: [GENERAL] Schemas and serials

Addendum: Some minutes ago, using EMS SQL Manager Lite, I tried what I was asking. First, I created a new schema. Then, I duplicated some of the transactional tables from the public schema, which is acting as a definition repository for those tables, to the new "transactional" schema. After th

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

Il 28 lug 2017 16:18, "Tom Lane" ha scritto: Vincenzo Romano writes: > I would like to understand the typo protection mentioned by Tom earlier: > I need to understand the reason for creating that special case. Well, case A: create function foo(out x int4) returns setof int8 ... Here the "set

Re: [GENERAL] Executing regex in C code

Thanks a lot, That did the trick Best regards On 30 July 2017 at 18:26, Tom Lane wrote: > Gabriel Furstenheim Milerud writes: > > I'm trying to execute the equivalent to '~' with regexp from within C > code. > > > text * s = cstring_to_text("aa"); > > text * p = cstring_to_text("a"); > >

Re: [GENERAL] Schemas and serials

Melvin: My example was somewhat inexact. The full question is as follows: I need to have two groups of tables: the "reference" ones (examples: city, country, customer) which will "reside" in the public schema, and the transaccional ones, which will reside in a schema representing one year/seas

Re: [GENERAL] Executing regex in C code

Gabriel Furstenheim Milerud writes: > I'm trying to execute the equivalent to '~' with regexp from within C code. > text * s = cstring_to_text("aa"); > text * p = cstring_to_text("a"); > return DatumGetBool(DirectFunctionCall2(textregexeq, PointerGetDatum(s), > PointerGetDatum(p))); > But

[GENERAL] Executing regex in C code

Hi, I'm trying to execute the equivalent to '~' with regexp from within C code. According to pg_operator this operator for type text corresponds to textregexeq: DATA(insert OID =