Hi,
With a server initdb'd with UTF8 encoding , if I create a table with a
client using LATIN1 encoding and later try to work with the relation
with a client using UTF8 encoding (both the scenarios simulated using
single session of psql but with different client_encoding set), there
is an error. F
On Tue, Oct 22, 2013 at 1:32 AM, AI Rumman wrote:
> Hi,
>
> Is there a way in Postgresql C function to get the connected session pointer
> ( Archive * AH)
> and use it for further execution?
If I read pg_archiver.c correctly, the AH pointer is used only during
the archiving and is not "globally"
Hey,
I tried something very similar to compute generalized union of numeric
range (which was innapropriate, anyway).
My conclusion were that it's not possible using windows function as you
need either a memory (windows function are not allowed in update) or
iterations to propagate information (win
Amit Langote wrote:
> With a server initdb'd with UTF8 encoding , if I create a table with a
> client using LATIN1 encoding and later try to work with the relation
> with a client using UTF8 encoding (both the scenarios simulated using
> single session of psql but with different client_encoding set
On 21/10/13 20:46, Tom Lane wrote:
> =?ISO-8859-1?Q?Torsten_F=F6rtsch?= writes:
>> > I noticed that with synchronous replication I often see locks like this:
>> > [ AccessExclusiveLock on "database 0" ]
> You did not say what PG version you're talking about, but if it's recent
It's 9.3.1
> then
Hi,
I'm reading code of xact.c, and I found the only difference between
TRANS_START
and TRANS_INPROGRESS is when transaction aborts in TRANS_START status we set
status to TRANS_INPROGRESS so AbortTransaction() will not report WARNING.
So I wonder to know the reason why we distinguish them?
DT wrote:
> I'm reading code of xact.c, and I found the only difference between
> TRANS_START
> and TRANS_INPROGRESS is when transaction aborts in TRANS_START status we set
> status to TRANS_INPROGRESS so AbortTransaction() will not report WARNING.
> So I wonder to know the reason why we distingui
On 10/22/13, Rémi Cura wrote:
> But it is immensely easier and sometimes mandatory to use instead
> a plpgsql function using cursor (or cursors).
>
> It would be something like that in plpgsql :
>
> cursor on table of letter ordered
> accum = 0;
> loop on rows of table ordered
>
> if letter = prev
Hey,
when using a for you implicitly use a cursor (I think),
so this is the same, use FOR if you like it more.
It should be *very* fast to write !
As I wrote, relational algebra can handle it, but it is not practically
feasible :
If you just execute 3 times the query I wrote, you will have your a
On Tue, Oct 22, 2013 at 8:16 AM, Rémi Cura wrote:
> Hey,
> when using a for you implicitly use a cursor (I think),
> so this is the same, use FOR if you like it more.
> It should be *very* fast to write !
>
> As I wrote, relational algebra can handle it, but it is not practically
> feasible :
>
>
On pon, paź 21, 2013 at 08:38:52 -0400, Robert James wrote:
> I have a table of event_id, event_time. Many times, several events
> happen in a row. I'd like a query which replaces all of those events
> with a single record, showing the count.
>
> Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return:
héhé,
nice snipping Merlin !
I guess you are almost there, output is still wrong (should be) (
> Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1;
> D,1; A,2; D,2; B,1; C,2
)
I don't understand enough to make the modifications =)
Cheers,
Rémi-C
2013/10/22 hubert depesz luba
Hey everyone,
This should be pretty straight-forward, but figured I'd pass it by anyway.
I have a revised backup process that's coming out inconsistent, and I'm not
entirely sure why. I call pg_start_backup(), tar.gz the contents elsewhere,
then pg_stop_backup(). Nothing crazy. Upon restore, tw
On Tue, Oct 22, 2013 at 8:41 AM, Rémi Cura wrote:
> héhé,
> nice snipping Merlin !
>
> I guess you are almost there, output is still wrong (should be) (
>> Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1;
>> D,1; A,2; D,2; B,1; C,2
> )
>
> I don't understand enough to make the
On 10/21/2013 11:41 PM, Luca Ferrari wrote:
On Tue, Oct 22, 2013 at 7:20 AM, James Sewell wrote:
I need a way to backup either from SQL in PSQL (possibly \!) or from a PG/PLSQL
function to a file with a name set from a :variable.
Could it be something like this:
# \setenv myFile 'filename'
On Tue, Oct 22, 2013 at 7:00 PM, Albe Laurenz wrote:
> Amit Langote wrote:
>> With a server initdb'd with UTF8 encoding , if I create a table with a
>> client using LATIN1 encoding and later try to work with the relation
>> with a client using UTF8 encoding (both the scenarios simulated using
>> s
Thanks for this good example Merlin !
I didn't know you could use variable inside custom aggregates, and this
allow to solve the problem!
In my own problem I couldn't use aggregates because
_as it output at most one row, it would have mean a lots of useless
computation (as in this example I guess
On Tue, Oct 22, 2013 at 9:09 AM, Rémi Cura wrote:
>
> Thanks for this good example Merlin !
>
> I didn't know you could use variable inside custom aggregates, and this
> allow to solve the problem!
>
> In my own problem I couldn't use aggregates because
> _as it output at most one row, it would ha
Thanks again for the precision !
I still don't understand perfectly. We call the aggregate n times, and each
time we compute the aggregate, using (potentially) n rows, thus becoming
(at most) O(n*n).
With a standard loop, I loop n times, and each times I only need the
current row plus the previou
On 2013-10-21 20:38, Robert James wrote:
I have a table of event_id, event_time. Many times, several events
happen in a row. I'd like a query which replaces all of those events
with a single record, showing the count.
Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1;
D,1; A,
No client connecting to the slave. It's just streamed replication for HA.
This occurs when the slave starts immediately. SSL is used. And as I
mentioned the libraries are identical on both slave and master.
Interestingly, another slave that replicates from master does not have
this issue. The
Shaun Thomas wrote:
> I have a revised backup process that's coming out inconsistent, and I'm not
> entirely sure why. I call
> pg_start_backup(), tar.gz the contents elsewhere, then pg_stop_backup().
> Nothing crazy. Upon restore,
> two of my tables report duplicate IDs upon executing my redact
On Tue, Oct 22, 2013 at 10:01 AM, Elliot wrote:
> On 2013-10-21 20:38, Robert James wrote:
>>
>> I have a table of event_id, event_time. Many times, several events
>> happen in a row. I'd like a query which replaces all of those events
>> with a single record, showing the count.
>>
>> Eg: Take A
Hmm exactly what I was thinking !
Thank you a lot, I spend many hours thinking about this and this solution
is very nice.
Cheers,
Rémi-C
2013/10/22 Merlin Moncure
> On Tue, Oct 22, 2013 at 10:01 AM, Elliot
> wrote:
> > On 2013-10-21 20:38, Robert James wrote:
> >>
> >> I have a table of even
> Wrong. The database cannot check all data for consistency
> upon backup. For one, that would take way too long.
Well, what I meant, was that it would stop the database if it couldn't
apply one of the transaction logs for whatever reason. It wasn't
"inconsistent enough" for that. :)
> If you
On Tue, Oct 22, 2013 at 9:43 AM, Rémi Cura wrote:
> Thanks again for the precision !
>
> I still don't understand perfectly. We call the aggregate n times, and each
> time we compute the aggregate, using (potentially) n rows, thus becoming (at
> most) O(n*n).
>
> With a standard loop, I loop n tim
OK,
just out of pure curiosity,
is it always the case or is it due to this particular aggregate?
Cheers,
Rémi-C
2013/10/22 Merlin Moncure
> On Tue, Oct 22, 2013 at 9:43 AM, Rémi Cura wrote:
> > Thanks again for the precision !
> >
> > I still don't understand perfectly. We call the aggregate
On 10/21/2013 10:20 PM, James Sewell wrote:
That looks great, but it doesn't really help with my problem unless
I'm missing something (very possible!)
I need a way to backup either from SQL in PSQL (possibly \!) or from a
PG/PLSQL function to a file with a name set from a :variable.
This wou
I have a libpq C interface code which takes user/password to connect to the
db. Now, I want to call it from a pgsql function where user will not need
to put any user name or password, rather it will use the current session ID.
How can I do it? Any idea, please.
Thanks.
On Tue, Oct 22, 2013 at 2:
AI Rumman writes:
> I have a libpq C interface code which takes user/password to connect to the
> db. Now, I want to call it from a pgsql function where user will not need
> to put any user name or password, rather it will use the current session ID.
> How can I do it? Any idea, please.
It sounds
Hey dear lists,
Here is a self contained example showing strange behavior from a real life
example concerning the use of postgis_topology topogeometry type.
The problem is :
when trying to return setof topogeometry,
the "return query" gives an error of type where there is none, and the
return n
Hi all.
My website is about to get a little more popular. I'm trying to add in
some measurements to determine an upper limit of how many concurrent
database connections I'm currently using.
I've started running this:
SELECT sum(numbackends) AS count, sum(xact_commit) as ttlcommit FROM
pg_s
Andy,
* andy (a...@squeakycode.net) wrote:
> My website is about to get a little more popular. I'm trying to add
> in some measurements to determine an upper limit of how many
> concurrent database connections I'm currently using.
PG is really *much* happier if you have only one backend per CPU
On 10/22/2013 12:59 PM, Stephen Frost wrote:
Andy,
* andy (a...@squeakycode.net) wrote:
My website is about to get a little more popular. I'm trying to add
in some measurements to determine an upper limit of how many
concurrent database connections I'm currently using.
PG is really *much* ha
* andy (a...@squeakycode.net) wrote:
> If I did plugin pg_bouncer, is it worth switching my php from
> pg_connect to pg_pconnect?
No, let pg_bouncer manage the connection pooling. Having two levels of
pooling isn't a good idea (and pg_bouncer does a *much* better job of it
anyway, imv..).
> I'd
On 10/22/2013 9:27 AM, AI Rumman wrote:
I have a libpq C interface code which takes user/password to connect
to the db. Now, I want to call it from a pgsql function where user
will not need to put any user name or password, rather it will use the
current session ID.
How can I do it? Any idea,
On 10/22/2013 10:59 AM, Stephen Frost wrote:
PG is really*much* happier if you have only one backend per CPU in your
system. The way to get there is by using a connection pooler like
pg_bouncer and configuring it based on how many CPUs you have.
Actually, I've found peak throughputs on a dece
On Mon, Oct 21, 2013 at 7:52 PM, David Johnston wrote:
It would help is Moshe would post a minimally viable working example of the
> entire use-case so that its desirability can be assessed and potential
> short-term alternative provided since even if desired this could not be
> released until 9.
On 10/22/2013 11:25 AM, andy wrote:
Hum.. I had not thought of that. My current setup uses 40 max
connections, and I don't think I've ever hit it. I use apache and
php, and my db connections are not persistent.
that style of php programming, you're getting some HUGE overhead in
connect/dis
On Tue, Oct 22, 2013 at 6:47 AM, Shaun Thomas wrote:
> Hey everyone,
>
> This should be pretty straight-forward, but figured I'd pass it by anyway.
>
> I have a revised backup process that's coming out inconsistent, and I'm
> not entirely sure why. I call pg_start_backup(), tar.gz the contents
> e
* John R Pierce (pie...@hogranch.com) wrote:
> On 10/22/2013 10:59 AM, Stephen Frost wrote:
> >PG is really*much* happier if you have only one backend per CPU in your
> >system. The way to get there is by using a connection pooler like
> >pg_bouncer and configuring it based on how many CPUs you h
> Hey dear lists,
> Here is a self contained example showing strange behavior from a real life
> example concerning the use of postgis_topology topogeometry type.
>
>
> The problem is :
> when trying to return setof topogeometry,
> the "return query" gives an error of type where there is none, a
> So you can grab the extra files, but you can't make it apply them,
> as you are telling it that it doesn't need to.
Do I have to, though? Replaying transaction logs is baked into the crash
recovery system. If I interrupt it in the middle of a checkpoint, it should be
able to revert to the prev
On 10/22/2013 2:18 PM, John R Pierce wrote:
On 10/22/2013 11:25 AM, andy wrote:
Hum.. I had not thought of that. My current setup uses 40 max
connections, and I don't think I've ever hit it. I use apache and
php, and my db connections are not persistent.
that style of php programming, you're
andy writes:
> On 10/22/2013 2:18 PM, John R Pierce wrote:
>> that style of php programming, you're getting some HUGE overhead in
>> connect/disconnect per web page.putting pg_bouncer in the middle
>> will make a HUGE improvement, possibly a second per page load on a busy
>> server.
> No, act
On 10/22/2013 1:13 PM, andy wrote:
No, actually, I don't think my connect overhead is huge. My apache
and postgres are on the same box, and it connects using unix socket.
Perhaps if my apache on db were on different boxes it would be a problem.
each postgres connection, if you're not using a
On 10/22/2013 3:44 PM, Tom Lane wrote:
andy writes:
On 10/22/2013 2:18 PM, John R Pierce wrote:
that style of php programming, you're getting some HUGE overhead in
connect/disconnect per web page.putting pg_bouncer in the middle
will make a HUGE improvement, possibly a second per page load
Hello All,
Thanks for the replies.Sorry I must have been a bit unclear, I realise I
*could* do this from the shell level, but can I do it from a PSQL session
somehow?
I think the answer is no (I assume unless I write my own C function or
similar).
It seems there is no way of passing a :variable
Wow, this is an excellent discussion - and I must admit, a bit beyond
my abilities. Is there a consensus as to the best approach to adopt?
Is Elliot's the best?
On 10/22/13, Rémi Cura wrote:
> OK,
> just out of pure curiosity,
> is it always the case or is it due to this particular aggregate?
>
Thanks much. It is of great help.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Upgrade-from-9-1-to-9-10-tp5775290p5775534.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.o
I am not a PostgresSQL user but would like to hear from users who have
configured their database to use the UTF-8 charset, especially any users
using the case and accent insensitive collation.
I am using a open source SQL server at this time using ISO8859_1 and
performance is excellent but I recre
On 10/22/2013 4:15 PM, Caroline Beltran wrote:
I am not a PostgresSQL user but would like to hear from users who have
configured their database to use the UTF-8 charset, especially any
users using the case and accent insensitive collation.
I am using a open source SQL server at this time using
On 10/22/2013 03:41 PM, James Sewell wrote:
Hello All,
Thanks for the replies.Sorry I must have been a bit unclear, I realise I
*could* do this from the shell level, but can I do it from a PSQL
session somehow?
Lucas' \setenv method won't work for you?
Cheers,
James Sewell,
PostgreSQL Tea
Oh I missed that, I skimmed and thought it was the same as \set
Turns out it's not and it's exactly what I want!
Thanks!
James Sewell,
PostgreSQL Team Lead / Solutions Architect
__
Level 2, 50 Queen St, Melbourne VIC 3000
*P *(+61) 3 8370 8000 * **W* www.
* John R Pierce (pie...@hogranch.com) wrote:
> On 10/22/2013 1:13 PM, andy wrote:
> >No, actually, I don't think my connect overhead is huge. My
> >apache and postgres are on the same box, and it connects using
> >unix socket. Perhaps if my apache on db were on different boxes it
> >would be a pro
On Tue, Oct 22, 2013 at 3:15 PM, Moshe Jacobson wrote:
>
> Here is the full code. It is not “minimal”, but actually what we are using.
> fn_get_create_or_update_space_sku() will create a non-existent row, or
> update it with the passed-in data if it already exists.
> You’ll notice that in this ver
On 10/22/2013 6:07 PM, Caroline Beltran wrote:
John, thank you for taking the time to respond. Performance wise, a 5%
difference is much better than I thought possible.
The only concern in regards to my post would be in regards to accent
character collation, i.e.:
Angel Smith
Ángel Smith
Ang
On 10/22/2013 12:59 PM, Stephen Frost wrote:
Andy,
* andy (a...@squeakycode.net) wrote:
My website is about to get a little more popular. I'm trying to add
in some measurements to determine an upper limit of how many
concurrent database connections I'm currently using.
PG is really *much* ha
> 2013/10/22 Merlin Moncure
>> > With a standard loop, I loop n times, and each times I only need the
>> > current
>> > row plus the previous row which I put in memory, thus O(n).
>>
>> For posterity, the above is incorrect. Since the aggregate is ordered
>> through the window function, it gets e
On 10/22/2013 7:37 PM, John R Pierce wrote:
And, as I said, there is also the contributed "CITEXT" extension,
distributed with the postgres core,
http://www.nytimes.com/intera...
which further simplifies this.
wow. I have NO idea how that link crept in there.
I thought I pasted
http://www.pos
On 10/22/2013 7:45 PM, Andy Colson wrote:
Ahh, bummer, man. PgBouncer doesn't work so well when you have lots
of databases. I have about 90 databases, the website could connect to
any one of them on any request. (They are all about as equally likely
to be hit)
that scenario would better b
try:
RETURN QUERY SELECT 1,1,1,1;
The error message means the cast failed between ttt.fake_topogeometry and
the topology_id (i.e. first) field of the return type of the function,
which isn't what you wanted to do.
Pls. don't cross-post between lists.
On 23 October 2013 01:21, Rémi Cura wrote
62 matches
Mail list logo
