Re: Is it possible to make a streaming replication faster using COPY instead of lots of INSERTS?

On Nov 30, 2011, at 18:44, Craig Ringer <ringerc <at> ringerc.id.au> wrote:

> On 11/30/2011 10:32 PM, Sergey Konoplev wrote:
>> Would it be more compact from the point of view of streaming
>> replication if we make the application accumulate changes and do one
>> COPY instead of lots of INSERTS say once a minute? And if it will be
>> so how to estimate the effect approximately?
> Streaming replication works on a rather lower level than that. It records information about transaction
starts, rollbacks and commits, and records disk block changes. It does not record SQL statements. It's
not using INSERT, so you can't switch to COPY. Streaming replication basically just copies the WAL data,
and WAL data is not all that compact.

I think a better way to phrase the question is whether these three types of constructs affect different
results on the replication side:

Insert into tbl values(...); [times 50]
insert into tbl values (...), (...), (...), ...; [ once with 50 values ]
Copy [ with 50 input rows provided ]

I would presume the first one is badly performing but no idea whether the multi-value version of insert
would be outperformed by an equivalent Copy command (both on the main query and during replication)

Though, does auto-commit affect the results in the first case; I.e., without auto-commit do the first two
results replicate equivalently?

> 
David J
--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:

Using a domain

Hello,

I'm trying to use a domain to define a data type constraint, let's say
an hypothetical uk_post_code with pattern LNNLL. I'd enforce no
whitespaces, all uppercase.

I would also need a way to normalize before validate: given an input
such as "w3 6bq", normalize it to W36BQ before trying to apply the
check. It would be great if I could give this function the same name
of the domain, so that uk_post_code('w3 6bq') would return W36BQ cast
to the domain.

Unfortunately it seems a domain implicitly defines a function, and
this function only perform the cast: the above is thus equivalent to
'w3 6bq'::uk_post_code, which would fail as the constraint doesn't
match. IIRC from when I've played with type definitions in C, for a
type there is no such automatic definition: a function converting text
to the type must be explicitly provided. \df doesn't show such
function for the domain (nor DROP FUNCTION seems knowing it), and if I
create one, it is not invoked (the cast takes precedence).

Is there any way to define a conversion in a function call
uk_post_code(text), or the only way to provide a normalization
function is to give it a different name (such as to_uk_post_code - I'd
like to know if there is a convention in how to name this function).

Is there any documentation about domains apart from the
CREATE/ALTER/DELETE commands? Haven't found any in the docs.

Thanks.

Re: Using a domain

Daniele Varrazzo <daniele.varrazzo <at> gmail.com> writes:
> I'm trying to use a domain to define a data type constraint, let's say
> an hypothetical uk_post_code with pattern LNNLL. I'd enforce no
> whitespaces, all uppercase.

> I would also need a way to normalize before validate: given an input
> such as "w3 6bq", normalize it to W36BQ before trying to apply the
> check. It would be great if I could give this function the same name
> of the domain, so that uk_post_code('w3 6bq') would return W36BQ cast
> to the domain.

That particular case isn't going to work unless you choose a different
function name --- as you've found out, the parser prefers the
interpretation that this means the same as 'w3 6bq'::uk_post_code,
which is not a cast but just a literal of the named type.

If you were willing to write something like uk_post_code('w3 6bq'::text)
and define your function as taking text (or varchar if that turns you on),
it should work.  Likewise anytime the argument is a variable/expression
of known type text.  But with a bare untyped literal, no.

			regards, tom lane

--

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

Problem with custom aggregates and record pseudo-type

Re: How to restore the table space tar files created by pg_basebackup?

Re: Is it possible to make a streaming replication faster using COPY instead of lots of INSERTS?

On 1 December 2011 03:44, Craig Ringer <ringerc <at> ringerc.id.au> wrote:
> Streaming replication works on a rather lower level than that. It records
> information about transaction starts, rollbacks and commits, and records
> disk block changes. It does not record SQL statements. It's not using
> INSERT, so you can't switch to COPY. Streaming replication basically just
> copies the WAL data, and WAL data is not all that compact.

My thought was about saving bytes on the information about transaction
starts, rollbacks and commits. I case of lost of small inserts each in
different transaction I suppose there will be more data like this.

> Try to run streaming replication over a compressed channel. PostgreSQL might
> gain the ability to do this natively - if someone cares enough to implement
> it and if it doesn't already do it without my noticing - but in the mean
> time you can use a compressed SSH tunnel, compressed VPN, etc.

Thank you for the advice.

> Alternately, investigate 3rd party replication options like Slony and
> Bucardo that might be better suited to your use case.
>
> --
> Craig Ringer

-- 
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com
LinkedIn: http://ru.linkedin.com/in/grayhemp
JID/GTalk: gray.ru <at> gmail.com Skype: gray-hemp

--

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

Re: Is it possible to make a streaming replication faster using COPY instead of lots of INSERTS?

On 1 December 2011 04:00, David Johnston <polobo <at> yahoo.com> wrote:
> On Nov 30, 2011, at 18:44, Craig Ringer <ringerc <at> ringerc.id.au> wrote:
>
>> On 11/30/2011 10:32 PM, Sergey Konoplev wrote:
> Insert into tbl values(...); [times 50]
> insert into tbl values (...), (...), (...), ...; [ once with 50 values ]
> Copy [ with 50 input rows provided ]
>
> I would presume the first one is badly performing but no idea whether the multi-value version of insert
would be outperformed by an equivalent Copy command (both on the main query and during replication)
>
> Though, does auto-commit affect the results in the first case; I.e., without auto-commit do the first two
results replicate equivalently?

So the guaranteed solutions are either

BEGIN;
INSERT INTO table1 VALUES (...), (...), ...;
COMMIT;

or

COPY FROM ...;

correct?

>
>>
> David J

-- 
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com
LinkedIn: http://ru.linkedin.com/in/grayhemp
JID/GTalk: gray.ru <at> gmail.com Skype: gray-hemp

--

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

Re: Limiting number of connections to PostgreSQL per IP (not per DB/user)?

On 29.11.2011 23:38, Merlin Moncure wrote:
> On Tue, Nov 29, 2011 at 7:49 AM, Heiko Wundram <modelnine <at> modelnine.org> wrote:
>> Hello!
>>
>> Sorry for that subscribe post I've just sent, that was bad reading on my
>> part (for the subscribe info on the homepage).
>>
>> Anyway, the title says it all: is there any possibility to limit the number
>> of connections that a client can have concurrently with a PostgreSQL-Server
>> with "on-board" means (where I can't influence which user/database the
>> clients use, rather, the clients mostly all use the same user/database, and
>> I want to make sure that a single client which runs amok doesn't kill
>> connectivity for other clients)? I could surely implement this with a proxy
>> sitting in front of the server, but I'd rather implement this with
>> PostgreSQL directly.
>>
>> I'm using (and need to stick with) PostgreSQL 8.3 because of the frontend
>> software in question.
>>
>> Thanks for any hints!
> 
> I think the (hypothetical) general solution for these types of
> problems is to have logon triggers.  It's one of the (very) few things
> I envy from SQL Server -- see  here:
> http://msdn.microsoft.com/en-us/library/bb326598.aspx.

I'd like to have logon triggers too, but I don't think that's the right
solution for this problem. For example the logon triggers would be
called after forking the backend, which means overhead.

The connection limits should be checked when creating the connection
(validation username/password etc.), before creating the backend.

Anyway, I do have an idea how this could be done using a shared library
(so it has the same disadvantages as logon triggers). Hopefully I'll
have time to implement a PoC of this over the weekend.

> Barring the above, if you can trust the client to call a function upon
> connection I'd just do that and handle the error on the client with a
> connection drop. Barring *that*, I'd be putting my clients in front of
> pgbouncer with some patches to the same to get what I needed
> (pgbouncer is single threaded making firewally type features quite
> easy to implement in an ad hoc fashion).

The connection pooler somehow easier and more complex at the same time.

You can use connect_query to execute whatever you want after connecting
to the database (not trusting the user to do that), but why would you do
that? But the database will see the IP of the pgbouncer, not the IP of
the original client. So executing the query is pointless.

You can modify pgbouncer and it should be quite simple, but you can
achieve different username/password (pgbouncer) to each customer,
different database, set pool_size for each of the connections. It won't
use IP to count connections, but the user's won't 'steal' connections
from the other.

Tomas

--

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

Re: Strange problem with turning WAL archiving on

BK wrote:
[server complains that wal_level is not set correctly]
>> Did you change the correct postgresql.conf?
>> Are there more than one lines for wal_level in the file
>> (try "grep wal_level postgresql.conf")?
> 
> I tried greping, there is just one nstance of it and is set on
archive.
> 
> Any other ideas what could have gone wrong in this strange situation?

Could you send me postgresql.conf (offlist) so that I can have a look at
it?

Yours,
Laurenz Albe

--

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

How to get Place Names from Lat Lon

Dear all,

I have a position table that contains the lat lon of an entity from time 
to time.
Now I want to get the place names from the respective lat lon.

In the past , I  am able to get the country names in which the lat lon 
falls because I have a table that contains the geom of all countries.
But now, I want to know the city name too fore.g Delhi , Bangalore , 
canada, netherland etc.

Is it possible, pls let me know.

Thanks

--

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