date:20170731

The comparison would be

a if then else end if .. about 8 of them 2013-> and a static insert into

v's

making a dynamic string and using execute, my presumption would be the
execute would be expensive verses a INSERT command

A

On 1 August 2017 at 07:04, Scott Marlowe  wrote:

> On Sun, Jul 30, 2017 at 7:13 PM, Alex Samad  wrote:
> > 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.
>
> Assuming you're not doing anything particularly crazy it's minimal.
> But what is good performance for one application may not be acceptable
> for others. Generally the cost of inserting is MUCH higher than the
> cost of dynamically setting the target, esp if you stick to plpgsql
> and don't try to use rules to accomplish it.
>

Re: [GENERAL] Question about loading up a table

Hi

I'm using pg_dump 9.6 to do the dumps.

I'm also pretty sure no data is being replicated until the end of the copy
stdin as I was watching tcpdump output and I can see data from the orig
master to the new master and no traffic between new master and the standby,
pretty sure my replication is working as my other tables have replicated
over.


as for allow pg_dump to create copy stdin with specific number of rows -
not sure what that is so hard / bad, if it was a option for somebody to
use.  For my situation its sounds like a really good idea.

I'm dumping a single table into a new single table so constraint shouldn't
be a problem.


Guess I have to just let it rung to completion

Thanks



On 1 August 2017 at 06:59, Scott Marlowe  wrote:

> On Mon, Jul 31, 2017 at 2:31 AM, vinny  wrote:
> > On 2017-07-31 11:02, Alex Samad wrote:
> >>
> >> Hi
> >>
> >> I am using pg_dump | psql to transfer data from my old 9.2 psql into a
> >> 9.6 psql.
>
> Note that you should be doing pg_dump with 9.6's pg_dump, as it's
> possible for 9.2's pg_dump to not know about a 9.6 feature.
>
> >> The new DB server is setup as master replicating to a hot standby
> >> server.
> >>
> >> What I have noticed is that the rows don't get replicated over until
> >> the copy from stdin is finished...  hard to test when you have M+ lines
> >> of rows.
>
> SNIP
>
> >> Is there a way to tell the master to replicate earlier
> >
> > I highly doubt it, because the master cannot know what to replicate until
> > your transaction is ended with a COMMIT. If you end with ROLLBACK,
> > or your last query is DELETE FROM (your_table>;
> > then there isn't even anything to replicate at all...
>
> This is actually a visibility issue. All the new changes are
> replicated to the slave, but just like on the master, other
> connections can't see the change because it's not visible. The slave,
> except for some small delay (seconds etc) is an exact replica of the
> master. So even a delete at the end gets replicated. You just don't
> see anything but possible table bloat to show for it.
>
> To prove this to oneself, start the copy, and get into another session
> to the master. You don't see any rows there either until the commit
> after the copy.
>

[GENERAL] PostgreSQL and Data Warehouse optimizations

2017-07-31 Thread Daniel Franco

Recently, I've been doing a research about data warehouse optimizations and
techniques. For example, Oracle uses parallel queries, materialized views
with different refresh modes, star transformations in queries, etc. Since
I've never worked with PostgreSQL in an OLAP environment, I thought maybe
the community could guide me to find what features and techniques
PostgreSQL provides for its users and it would be especially useful if
someone could point out a previous work comparing PostgreSQL with another
RDBMs in this regard. Also, I'm interested if there's any interesting
books, articles or anything related PostgreSQL and Data Warehouses
optimizations.

Thanks and Regards for all the community.


Livre
de vírus. www.avast.com
.
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: [GENERAL] Partitioned TEMP tables

2017-07-31 Thread Tom Lane

Ed Behn  writes:
> I tried that. I didn't seem to help.

Well, it works in a simple test case for me.  You'll need to post a
self-contained example that's not working if you want much help ...

regression=# create temp table pp(f1 int, f2 text);
CREATE TABLE
regression=# create temp table c1(check(f1 between 0 and 1)) inherits(pp);
CREATE TABLE
regression=# create temp table c2(check(f1 between 1 and 2)) inherits(pp);
CREATE TABLE
regression=# explain select * from pp where f1 < 1;
 QUERY PLAN 

 Append  (cost=0.00..25.88 rows=424 width=36)
   ->  Seq Scan on pp  (cost=0.00..0.00 rows=1 width=36)
 Filter: (f1 < 1)
   ->  Seq Scan on c1  (cost=0.00..25.88 rows=423 width=36)
 Filter: (f1 < 1)
(5 rows)

regression=# explain select * from pp where f1 > 1;
 QUERY PLAN 

 Append  (cost=0.00..25.88 rows=424 width=36)
   ->  Seq Scan on pp  (cost=0.00..0.00 rows=1 width=36)
 Filter: (f1 > 1)
   ->  Seq Scan on c2  (cost=0.00..25.88 rows=423 width=36)
 Filter: (f1 > 1)
(5 rows)


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] Manage slot in logical/pglogical replication

On Mon, Jul 17, 2017 at 7:08 AM, dpat  wrote:
> yes i have estimated pg_xlog but server, some time, writes a lot of WAL file.
> there are some robust alternative to replicate partial database in
> Postgresql over WAN?
> or, what's the best way to realign pglogical replica? pg_dump/restore?.
> i have to find the best way to replicate only some table over internet to
> remote database.

That's been a job for slony in the past. Now pg_logical seems to be
getting ready / is ready to replace it.


-- 
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] upsert and update filtering

2017-07-31 Thread armand pirvu



That's because the access on this case is done to the existing row using the 
table's name / alias , and to the rows that we attempt to insert using the 
excluded 

Thank you Peter


Armand

> On Jul 31, 2017, at 4:31 PM, Peter Geoghegan  wrote:
> 
> armand pirvu  wrote:
>> But what if in the conflict situation I want to performa the update ONLY if 
>> the record is different. The update seems to happen no matter what
>> In other words is there anyway I can filter the update to happen (based on 
>> the sample date) only for 112 since col2 is different ?
> 
> That's quite possible. An ON CONFLICT's UPDATE accepts a WHERE clause,
> which can reference both existing and excluded tuples. That WHERE clause
> can back out of the UPDATE based on whatever criteria you like.
> 
> -- 
> Peter Geoghegan



-- 
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] upsert and update filtering

2017-07-31 Thread Peter Geoghegan


armand pirvu  wrote:

But what if in the conflict situation I want to performa the update ONLY if the 
record is different. The update seems to happen no matter what
In other words is there anyway I can filter the update to happen (based on the 
sample date) only for 112 since col2 is different ?


That's quite possible. An ON CONFLICT's UPDATE accepts a WHERE clause,
which can reference both existing and excluded tuples. That WHERE clause
can back out of the UPDATE based on whatever criteria you like.

--
Peter Geoghegan


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] upsert and update filtering

2017-07-31 Thread armand pirvu

Hi



create table dimc1 
(
col1 integer not null,
col2 char(10),
primary key (col1)
);



create table dimc2 
(
col1 integer not null,
col2 char(10),
primary key (col1)
);

testdb=# select * from dimc1 order by 1;
 col1 |col2
--+
  111 | foo111
  112 | foo112
(2 rows)

testdb=# select * from dimc2 order by 1;
 col1 |col2
--+
  111 | foo111
  112 | foo122
  211 | foo211


In general it is my understanding it gows like this


insert into dimc1  select * from dimc2  
on conflict (col1) do update 
SET  
col2 = EXCLUDED.col2
;


insert into dimc1  select * from dimc2  
on conflict (col1) do update 
SET  
col2 = EXCLUDED.col2 returning *
;



So far so good

But what if in the conflict situation I want to performa the update ONLY if the 
record is different. The update seems to happen no matter what
In other words is there anyway I can filter the update to happen (based on the 
sample date) only for 112 since col2 is different ? 



testdb=# select * from dimc1 order by 1;
 col1 |col2
--+
  112 | foo112

testdb=# select * from dimc2 order by 1;
 col1 |col2
--+
  112 | foo122




Thanks
Armand



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] standby database crash

2017-07-31 Thread Seong Son (US)

I have a client who has streaming replication setup with the primary in one 
city and standby in another city both identical servers with Postgresql 9.6 on 
Windows Server 2012.

They have some network issues, which is causing the connection from the primary 
to standby to drop sometimes.  And recently standby crashed with the following 
log.  And it could not be restarted.

2017-07-18 09:21:13 UTC FATAL:  invalid memory alloc request size 4148830208
2017-07-18 09:21:14 UTC LOG:  startup process (PID 5608) exited with exit code 1
2017-07-18 09:21:14 UTC LOG:  terminating any other active server processes
2017-07-18 09:21:14 UTC LOG:  database system is shut down

Last entry from the pg_xlogdump shows the following

pg_xlogdump: FATAL:  error in WAL record at D5/D1BD5FD0: 
unexpected pageaddr D1/E7BD6000 in log segment 00D500D1, offset 
12410880

So my questions are, could an old WAL segment being resent through the network 
cause crash like this?  Shouldn't Postgresql be able to handle out of order WAL 
segments instead of just crashing?

And what would be the best way to recover the standby server?  Resynching the 
entire database seems to be too time consuming.

Thanks in advance for any info.

-Seong

Re: [GENERAL] Schemas and serials

On Sat, Jul 29, 2017 at 1:17 PM, Melvin Davidson 
wrote:

>
> On Sat, Jul 29, 2017 at 3:38 PM, tel medola  wrote:
>
>> Depends.
>> When you create your tables in new schema, the script was the same from
>> "qa"?
>> Sequences, tables, etc.. belong to the schema where was created.
>>
>> Roberto.
>>
>> Em sáb, 29 de jul de 2017 às 16:17, marcelo 
>> escreveu:
>>
>>> Some days ago I asked regarding tables located in different schemas.
>>> Now, my question is
>>> Suppose I have two schemas (other than public): "qa" and "production".
>>> Initially I create all my tables in "qa". All of them have a primary key
>>> of type serial.
>>> Later, I will copy the tables definitions to production.
>>> It will automatically create the sequences in the new schema, starting
>>> at zero?
>>> TIA
>>> Marcelo
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>
>
>
>
> *Marcelo,>Initially I create all my tables in "qa". All of them have a
> primary key of type serial. >Later, I will copy the tables definitions to
> production.*
>
> *A word of caution, creating tables in a qa "schema" and then transferring
> to production is not the normal/correct (or safe) way to do development.*
>
> *The standard procedure is to create a seperate "qa" database (and/or
> server) with the exact same schema(s) as production. Then, after testing *
>
> *is completed, the schemas/tables are copied to production.*
>
>
This. Staging should look as much like production as possible for testing
purposes. If you HAVE to use the same server (not a good idea) then make
separate clusters. Separate servers here can save you a LOT of heartache
from someone fat fingering a script meant for staging hitting production.

Re: [GENERAL] partitioning question

On Sun, Jul 30, 2017 at 7:13 PM, Alex Samad  wrote:
> 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.

Assuming you're not doing anything particularly crazy it's minimal.
But what is good performance for one application may not be acceptable
for others. Generally the cost of inserting is MUCH higher than the
cost of dynamically setting the target, esp if you stick to plpgsql
and don't try to use rules to accomplish it.

-- 
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 about loading up a table

On Mon, Jul 31, 2017 at 2:31 AM, vinny  wrote:
> On 2017-07-31 11:02, Alex Samad wrote:
>>
>> Hi
>>
>> I am using pg_dump | psql to transfer data from my old 9.2 psql into a
>> 9.6 psql.

Note that you should be doing pg_dump with 9.6's pg_dump, as it's
possible for 9.2's pg_dump to not know about a 9.6 feature.

>> The new DB server is setup as master replicating to a hot standby
>> server.
>>
>> What I have noticed is that the rows don't get replicated over until
>> the copy from stdin is finished...  hard to test when you have M+ lines
>> of rows.

SNIP

>> Is there a way to tell the master to replicate earlier
>
> I highly doubt it, because the master cannot know what to replicate until
> your transaction is ended with a COMMIT. If you end with ROLLBACK,
> or your last query is DELETE FROM (your_table>;
> then there isn't even anything to replicate at all...

This is actually a visibility issue. All the new changes are
replicated to the slave, but just like on the master, other
connections can't see the change because it's not visible. The slave,
except for some small delay (seconds etc) is an exact replica of the
master. So even a delete at the end gets replicated. You just don't
see anything but possible table bloat to show for it.

To prove this to oneself, start the copy, and get into another session
to the master. You don't see any rows there either until the commit
after the copy.

-- 
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] Partitioned TEMP tables

2017-07-31 Thread Tom Lane

Ed Behn  writes:
> Does partitioning of TEMP tables not work like non-TEMP tables?

Should be the same ... but you don't get any auto-analyze support on
a temp table.  I wonder if you're remembering to ANALYZE the temp
tables after you've populated them.

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] Dealing with ordered hierarchies

2017-07-31 Thread Peter J. Holzer

On 2017-07-25 01:15:56 +1200, Tim Uckun wrote:
> I don't like the approach with a large increment. It would mean complicated
> logic to see if you filled the gap and then update all the other peers if you
> did. It sounds like the re-order is going to be expensive no matter what. My
> primary concern are race conditions though. What if two or more users are
> trying to update the hierarchy either by inserts or updates? I can definitely
> see a situation where we have issues transactions trip over each other.

You could add a unique index over (parent, sequence_number). That way
two transactions won't be able to add a node with the same sequence
number under the same parent. You will have to handle duplicate key
errors, though.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: Digital signature

[GENERAL] Partitioned TEMP tables

2017-07-31 Thread Ed Behn

I have an issue regarding partitioned TEMP tables.

I have a database with a number of families of tables partitioned by day as
described in section 5.10 of the User's Manual. I have an empty parent
tables each with a number of child tables containing data partitioned by
date. Each child has a CHECK condition on the date of the data. This works
fine when I execute a SELECT statement against the parent table and specify
a value for the date.

However, I am currently working on a system that requires me to create a
family of TEMP tables with the same setup. So, I have an empty TEMP parent
with each day’s data in a TEMP child.

The thing is that if I try try to run SELECT against the parent table with
the date specified in the WHERE clause, I get terrible performance despite
the fact that an EXPLAIN of the query looks fine. (The query can run for an
hour and I finally give up and kill it.) However, if I specify the child
table directly in the FROM clause, the query runs fine. (It only takes a
few minutes.)

Does partitioning of TEMP tables not work like non-TEMP tables? In the same
query, I access the parent table of a non-TEMP family and that doesn’t
cause problems.

Any idea what’s going on here?
-Ed



*Ed Behn */ Staff Engineer / Airline and Network Services
Information Management Services
2551 Riva Road, Annapolis, MD 21401 USA
Phone: (410)266-4426 / Cell: (240)696-7443
ed.b...@rockwellcollins.com

www.rockwellcollins.com

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

2017-07-31 Thread Dmitry Lazurkin

On 31.07.2017 19:42, Jeff Janes wrote:
> I think it is simply because no one has gotten around to implementing
> it that way.  When you can just write it as a values list instead, the
> incentive to make the regular in-list work better is not all that strong.
>
> Cheers,
>
> Jeff

I see from explain that IN-clause uses just array with function ANY. I
think for efficient implementation of this task I should implement new
datatype "hashset". Am I wrong?

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 8:03 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Jul 24, 2017 at 7:58 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane  wrote:
>>
>>>
>>> The cost to form the inner hash is basically negligible whether it's
>>> de-duped or not, but if it's not (known) de-duped then the cost
>>> estimate for the semijoin is going to rise some, and that discourages
>>> selecting it.
>>>
>>
>> Why does the "hash semi join" care about duplication of values on the
>> inner relation?  Doesn't it only care whether a given bucket exists
>> irrespective of its contents?
>>
>
> Rather, it cares about the contents is-so-far as confirming that at least
> one of the tuples in the bucket indeed has the same joining value as the
> outer relation (lost track of the fact that two values can share the same
> hash).  But once it finds one it can move onto the new outer relation tuple
> while an inner join would have to spend more time looking for additional
> matches.
>

What I gathered from the code comments from the last time I dug into
something like this, the main reason to try to de-dup and then use a join,
rather than a semi-join, is that doing it that way allows us to swap the
order of the rels in the join, which then opens other avenues for
optimization.  For example, "A join (B semijoin C)" could become "A join (B
join dedupC)" which could become "(dedupC join A) join B".  But if we don't
actually adopt the swap, then it does seem like it should retain the
semi-join.   Why continue digging through the hash collision chain lookin
for key collisions that can't exist? I don't know, maybe there are some
bits set that make it still do semi-join, just doesn't present itself as
such?

Cheers,

Jeff

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane  wr
>
>
> regression=# EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ids WHERE id
> IN
> :values_clause;
> QUERY PLAN
> 
> ---
>  Aggregate  (cost=245006.16..245006.17 rows=1 width=8) (actual
> time=3550.581..3550.581 rows=1 loops=1)
>  Execution time: 3550.700 ms
>
>


>
> regression=# set enable_hashagg TO 0;
> regression=# set enable_sort TO 0;
> SET
> regression=# EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ids WHERE id
> IN
> :values_clause;
>   QUERY PLAN
> 
> ---
>  Aggregate  (cost=320003.90..320003.91 rows=1 width=8) (actual
> time=3548.364..3548.364 rows=1 loops=1)
>  Execution time: 3548.463 ms
>
>


> At least in this example, the actual runtimes are basically identical
> regardless, so there is no great point in sweating over it.
>


Since The run times are equal, but one is estimated to be 30% more
expensive, I think there is at least some little reason to sweat over it.

Incidentally, I accidentally ran this against a server running with your
patch from
https://www.postgresql.org/message-id/10078.1471955...@sss.pgh.pa.us.  On
that server, it did choose the semi-join.  But I have no idea why, as it
seems like the effect of that patch would have been to change the distinct
estimate from the magic hard-coded 200, to the natural 200 coming from the
query itself.  Why would that affect the cost?

Cheers,

Jeff

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Tue, Jul 25, 2017 at 2:03 AM, Dmitry Lazurkin  wrote:

> On 25.07.2017 05:50, Jeff Janes wrote:
>
>> It isn't either-or.  It is the processing of millions of rows over the
>> large in-list which is taking the time. Processing an in-list as a hash
>> table would be great, but no one has gotten around to it implementing it
>> yet.  Maybe Dmitry will be the one to do that.
>>
>
> Thanks. Yes, I want. But... Is this task too complex for novice?
>

Yes, it is probably too complex for a novice.  On the other hand, you might
not be a novice anymore once you complete it!

Cheers,

Jeff

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 3:12 PM, Dmitry Lazurkin  wrote:

> On 25.07.2017 00:31, David G. Johnston wrote:
>
>
> Basically you want to write something like:
>
> SELECT *
> FROM ids
> JOIN ( :values_clause ) vc (vid) ON (vc.vid = ids.id)
>
> or
>
> WITH vc AS (SELECT vid FROM  ORDER BY ... LIMIT )
> SELECT *
> FROM ids
> JOIN vc ON (vid = ids.id)
>
>
> This query uses JOIN plan node as IN (VALUES ...).
>
> And I have one question. I don't understand why IN-VALUES doesn't use
> Semi-Join? PostgreSQL has Hash Semi-Join...  For which task the database
> has node of this type?
>
>
I think it is simply because no one has gotten around to implementing it
that way.  When you can just write it as a values list instead, the
incentive to make the regular in-list work better is not all that strong.

Cheers,

Jeff

Re: [GENERAL] Row based permissions: at DB or at Application level?

2017-07-31 Thread Thomas Güttler

Am 27.07.2017 um 12:05 schrieb vinny:

On 2017-07-27 10:27, Thomas Güttler wrote:

Am 25.07.2017 um 12:59 schrieb vinny:

On 2017-07-25 11:40, Thomas Güttler wrote:

I would like to reduce the "ifing and elsing" in my python code (less
conditions, less bugs, more SQL, more performance)

When I look at an example policy from the manual:

CREATE POLICY fp_u ON information FOR UPDATE
USING (group_id <= (SELECT group_id FROM users WHERE user_name =
current_user));

I'm not sure if this is any less bug-sensitive than an IF in Python...

Somehow I trust set operations more then "if" and "else" in a
programming language.

I understand the feeling, but realistically; you're doing exactly the same,
just in a different syntax.

And don't forget you have to interpret any error-response from the database into
something that Django can make understandable to the end-user.

But maybe I misunderstood what you mean with "error-response from the
database".

Indeed you did :-)

row-level security will cause the database to start returning permission-denied
messages,
for example:
(taken from the manual)

postgres=> update passwd set shell = '/bin/xx';
ERROR: new row violates WITH CHECK OPTION for "passwd"

Your application will have to be able to translate that error into something
that the user can understand.
In this case it should be something like "Sorry, this password is not allowed".

I see. I could just pass the error unchanged to the user ... Would look ugly ...

My current concer: I want a SELECT statement wich returns all rows a
user is allowed to see.

Sure, but the permissions in your application are not just like "John can see rows 1-4 and Pete can see rows that have
isVisible=True"

In a forum you may have a rule that says that posting new topics is only
allowed if you have posted more than ten replies,
and the forum allows now posts and the user is not banned. So the row-level
permission has to check the user and the forum
to decide what to do, and those rulings cannot be done using row-level security
so you will have to write pgsql functions
that do those checks on those records and well the whol row-level thing
turns into a group of functions very quickly.

Yes, you write what I had in mind. Since it is easy to extend the permission
checks in a programming language (example
you are allowed if you have posted more than ten replies) you soon have many
functions in your programming language.

This is easy and gets done everywhere.

As soon as you need the permission check in a SQL WHERE the whole "I love
programming and typing" way of doing things
get a very complex SQL WHERE condition. Yes in theory you can translate every
if-then-else checking from your
code to SQL.

I don't want to change my existing systems, which I currently develop and
maintain.

But if I start from scratch, then why not put all that stuff into SQL ...

This is brainstorming and I am just trying to widen my horizont.
Feedback welcome!

Ditto, I'd love to hear input from others!

Yes

Regards,
Thomas Güttler

--
Thomas Guettler http://www.thomas-guettler.de/

--
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 about loading up a table

2017-07-31 Thread vinny


On 2017-07-31 11:02, Alex Samad wrote:

Hi

I am using pg_dump | psql to transfer data from my old 9.2 psql into a
9.6 psql.

The new DB server is setup as master replicating to a hot standby
server.

What I have noticed is that the rows don't get replicated over until
the copy from stdin is finished...  hard to test when you have M+ lines
of rows.


If you are "just testing" then you could use the COPY command 
https://www.postgresql.org/docs/9.2/static/sql-copy.html

to generate a smaller dataset.




Is there a way to tell the master to replicate earlier


I highly doubt it, because the master cannot know what to replicate 
until

your transaction is ended with a COMMIT. If you end with ROLLBACK,
or your last query is DELETE FROM (your_table>;
then there isn't even anything to replicate at all...



or is there a
way to get pg_dump to bundle into say 100K rows at a time ?


I'm not aware of such a feature, it would be quite tricky because
of dependencies between records. You cannot simply dump the first 100k 
rows
from table A and the first 100k from table B, because row #9 from table 
A

may have a relation to row 100.001 from table B.


--
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 about loading up a table

Hi

I am using pg_dump | psql to transfer data from my old 9.2 psql into a 9.6
psql.

The new DB server is setup as master replicating to a hot standby server.

What I have noticed is that the rows don't get replicated over until the
copy from stdin is finished... hard to test when you have M+ lines of rows.

Is there a way to tell the master to replicate earlier or is there a way to
get pg_dump to bundle into say 100K rows at a time ?

Thanks
Alex

Re: [GENERAL] vacuum on streaming replication