Alexander Farber wrote:
> I have prepared an SQL fiddle for my question:
> http://sqlfiddle.com/#!11/8a494/4
>
> And also described it in more detail at
> http://stackoverflow.com/questions/15500270/string-matching-in-insert-trigger-how-to-use-in-
> conditionals-to-return-null
>
> Does anybody pl
Hi;
I have set up a windows domain trying to run Postgres client using SSPI. I've
reached a point that I can use psql to login from my client machine without
specifying username and password. But everytime I ran my .NET client program on
a domain computer other than the database server itself,
Hi,
I have a PostgreSQL database with 50 tables.
Every minute, sequentially, a batch load 10.000 rows of 250 bytes with a COPY.
After a day, i got a database with 50 tables with 1.440 set of 10.000 rows.
The tables are cleany and naturally clustered by the inserted timestamp.
Each table has data
Hi Guys,
We're seeing a problem with some of our FreeBSD/PostgreSQL servers
"leaking" quite significant amounts of disk space:
> df -h /usr/local/pgsql/
Filesystem SizeUsed Avail Capacity Mounted on
/dev/mfid1s1d1.1T772G222G78%/usr/local/pgsql
> d
Did you do a detailed du during the supposed problem and after the reboot and
make a diff of those
to fimd any invlolved files/dirs?
That said, i think you might consider posting on freebsd-[questions|stable] as
well.
On Τετ 20 Μαρ 2013 11:49:07 Dan Thomas wrote:
Hi Guys,
We're seeing a proble
> Did you do a detailed du during the supposed problem and after the reboot and
> make a diff of those to fimd any invlolved files/dirs?
du doesn't show the space in question (du -s shows the actual usage on
disk, df is showing a much higher number), so I doubt this will show
anything up. However
Hi;
I have set up a windows domain trying to run Postgres client using SSPI.
I've reached a point that I can use psql to login from my client machine
without specifying username and password. But everytime I ran my .NET client
program, I got following error messages. Any help is much appreciated!
On Τετ 20 Μαρ 2013 12:47:39 Dan Thomas wrote:
> > Did you do a detailed du during the supposed problem and after the reboot
> > and make a diff of those to fimd any invlolved files/dirs?
>
> du doesn't show the space in question (du -s shows the actual usage on
> disk, df is showing a much higher
On Wed, Mar 20, 2013 at 7:13 AM, jg wrote:
> Now, there is a partition rotation script, that suppress old tables when
> some size limit happens.
> Let suppose, that this script runs and suppress only one table qith few
> days of data, then recreates a new empty one.
>
It sounds like you are usin
Hi,
> It sounds like you are using partitioned tables. your partitions should be
> divided up such that they help optimize your queries. that is, minimize the
> number of partitions you need to scan for any given query.
>
> That said, try to make is so that this cleanup script purges whole
> part
On Wed, Mar 20, 2013 at 7:49 AM, Dan Thomas wrote:
> Not all of our servers are leaking space, it's only the more
> recently-installed systems. Here's a quick breakdown of versions:
>
FWIW, I do not observe this behavior. My database has very heavy write
load, and old data is purged after it is
On Wed, Mar 20, 2013 at 9:53 AM, jg wrote:
> The rotated script, as explained, just drops tables and creates empty ones.
>
That doesn't make sense then, to have fragmentation if you are creating new
tables with fresh data copied into them. The files should be pretty much
sequentially written.
Alexander Farber wrote:
> It seems to be better in 9.2.x?
Yes, as Tom has pointed out.
I didn't see that you were on 8.4 when I wrote my answer.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.
Alexander Farber writes:
> Thanks for trying! I am using CentOS 6.3
> It seems to be better in 9.2.x?
As stated upthread, 8.4 doesn't understand \u escapes. You'd need to
put in the characters another way --- either literally, or using octal
escapes to spell out the UTF8 encoding. I think it wi
On 03/20/2013 07:14 AM, Vick Khera wrote:
On Wed, Mar 20, 2013 at 9:53 AM, jg mailto:j...@rilk.com>>
wrote:
The rotated script, as explained, just drops tables and creates
empty ones.
That doesn't make sense then, to have fragmentation if you are creating
new tables with fresh data co
Hi,
> That doesn't make sense then, to have fragmentation if you are creating new
> tables with fresh data copied into them. The files should be pretty much
> sequentially written.
>
> O I see. You're using Windows. Maybe you need some OS with a better
> file system that doesn't fragment
jg, 20.03.2013 12:13:
I suspect the heavy fragmented files to the cause of the IO wait
grows (PostgreSQL on WIndows).
How to cope with that?
I would first investigate that it's *really* the fragmentation.
As a database does a lot of random IO, fragmentation isn't such a big issue.
You could u
regarding journaling, there is the counter argument that you do not need to do
the same job twice,
in the sense that we already spend a considerable amount of time retaining the
WAL in postgresql,
no need to redo the same on FS level.
"Crush"-intensive systems (for lack of a better word) might be
On Wed, Mar 20, 2013 at 10:34 AM, Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:
> regarding journaling, there is the counter argument that you do not need
> to do the same job twice,
>
> in the sense that we already spend a considerable amount of time retaining
> the WAL in postgresql,
Hi,
I create a test cas on Linux:
postgres=# create table a (v int);
postgres=# create table b (v int);
Then a while(true) over the following script where 24577 and 24580 are the
files of the tables a and b
#!/bin/sh
psql test -c 'insert into a select generate_series(1,10,1);'
psql test -c
Of course, but does it make sense for you to pay the ~ 5%/day performance
penalty for the ~0.5%/year chance of having your system crush?
Unless your FreeBSD server is stuffed with exotic gamer hardware, i don't see
the likehood of crush getting larger than that.
On Τετ 20 Μαρ 2013 10:39:58 Vick
> How long does it take for you to accumulate this "leak"?
It grows at between 2 and 4 gigabytes per day on average. It seems to
be related to load on the database, as it grows slower over the
weekends when the servers are under less load. Here's a graph that
shows growth of one server (from reboo
On Ôåô 20 Ìáñ 2013 15:15:23 Dan Thomas wrote:
>
> We actually have another FreeBSD8.3/PG9.1 machine under different (but
> similar) load that *doesn't* demonstrate this behaviour. There's
> nothing obvious in the differences in usage patterns that we can see
> (we're not using any exotic features
Hi,
Atfer 30 minutes, on my Linux computer, with 2 files fill one after the other.
I got a fragmented files with many back step:
# /usr/sbin/filefrag -v 24586
Filesystem type is: ef53
File size of 24586 is 822231040 (200740 blocks, blocksize 4096)
ext logical physical expected length flags
0
> Any difference in the architecture of the two systems? (x86, amd64, etc..)
> Any difference in the respective output of
> % pg_config
Alas, no. Both identical machines running identical versions of
FreeBSD and PG. pg_config on the two machines matches exactly.
On 20 March 2013 15:37, Achilleas
Dan Thomas wrote:
> We're seeing a problem with some of our FreeBSD/PostgreSQL
> servers "leaking" quite significant amounts of disk space:
> Stopping Postgres doesn't fix it, but rebooting does which points
> at the OS rather than PG to me. However, the leak is only
> apparent in the dedicated
On 03/20/2013 01:25 PM, Kevin Grittner wrote:
I saw something once which *might* be related. I don't recall the
OS of FS involved, but in an attempt to reduce the fragmentation of
files which started small and eventually grew large, a large
allocation of contiguous space was made on file creati
PostgreSQL 8.4.16 on CentOS 5.9.
I've run into a situation where executing a \COPY from psql will hang
and at that point it's impossible to terminate the COPY command.
I've tried pg_cancel_backend and pg_terminalte_backend - even sending
the process itself a TERM signal.
Sending the process KILL
I'm running into this exact situation:
http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7ojq_ktadrzdgd45ju7gke3duz0sz...@mail.gmail.com
We really need to be able to have a group of developers who can create
things and modify each others' stuff[1]. Is it still more or less
impossible?
The
Michael Orlitzky wrote:
> I'm running into this exact situation:
>
> http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7ojq_ktadrzdgd45ju7gke3duz0sz...@mail.gmail.com
>
> We really need to be able to have a group of developers who can create
> things and modify each others' stuff[1]. Is it
2013/3/20 David Rees :
> PostgreSQL 8.4.16 on CentOS 5.9.
>
> I've run into a situation where executing a \COPY from psql will hang
> and at that point it's impossible to terminate the COPY command.
>
> I've tried pg_cancel_backend and pg_terminalte_backend - even sending
> the process itself a TER
On 03/20/2013 04:12 PM, Alvaro Herrera wrote:
> Michael Orlitzky wrote:
>> I'm running into this exact situation:
>>
>> http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7ojq_ktadrzdgd45ju7gke3duz0sz...@mail.gmail.com
>>
>> We really need to be able to have a group of developers who can creat
For a while now I've known that sending a SIGINT can trigger a CHECKPOINT, but
I don't know if this is intentional or a bug. Logs from today:
> 2013-03-20_18:21:43.27642 LOG: received fast shutdown request
> 2013-03-20_18:21:43.27645 LOG: aborting any active transactions
> 2013-03-20_18:21:43.2
On Wed, Mar 20, 2013 at 1:12 PM, Martín Marqués
wrote:
> What happens if you use COPY ... FROM with the same data?
I will try that, but if you look at pg_stat_activity the full command
is a COPY ( Are you sure the process hangs (strange thing is that you can't
> terminate the backend)? Could it
On 3/20/13 4:28 PM, Sean Chittenden wrote:
> For a while now I've known that sending a SIGINT can trigger a CHECKPOINT,
> but I don't know if this is intentional or a bug.
It's intentional. If you don't want that, use SIGQUIT. That's how they
are different.
Of course, when using SIGQUIT, you w
On 03/20/2013 02:24 PM, Michael Orlitzky wrote:
On 03/20/2013 04:12 PM, Alvaro Herrera wrote:
Michael Orlitzky wrote:
I'm running into this exact situation:
http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7ojq_ktadrzdgd45ju7gke3duz0sz...@mail.gmail.com
We really need to be able to hav
On Wed, Mar 20, 2013 at 12:37 PM, David Rees wrote:
> PostgreSQL 8.4.16 on CentOS 5.9.
>
> I've run into a situation where executing a \COPY from psql will hang
> and at that point it's impossible to terminate the COPY command.
Some additional notes:
Running psql on the same LAN for some reason
David Rees writes:
> On Wed, Mar 20, 2013 at 12:37 PM, David Rees wrote:
>> PostgreSQL 8.4.16 on CentOS 5.9.
>>
>> I've run into a situation where executing a \COPY from psql will hang
>> and at that point it's impossible to terminate the COPY command.
> Some additional notes:
> Running psql o
On 03/20/2013 05:18 PM, Rob Sargent wrote:
> What's your process? First I've heard of a group of dev's ignorant of
> permission _and_ trusted to change things in a db which affect others.
It's a playground for a group of people. They want to be able to create
stuff, and then modify that stuff. N
On 03/20/2013 03:26 PM, Michael Orlitzky wrote:
On 03/20/2013 05:18 PM, Rob Sargent wrote:
At the moment, everyone's just experimenting. Even with the proper
tooling, my blog app shouldn't have to handle the database permissions
table-by-table. I should be able to set up sensible defaults.
On 03/20/2013 06:40 PM, Adrian Klaver wrote:
> On 03/20/2013 03:26 PM, Michael Orlitzky wrote:
>> On 03/20/2013 05:18 PM, Rob Sargent wrote:
>
>>
>> At the moment, everyone's just experimenting. Even with the proper
>> tooling, my blog app shouldn't have to handle the database permissions
>> table
I got this table right now:
select * from market_segment_dimension_values ;
+--+---+
| market_segment_dimension | value |
+--+---+
| geography| north |
| geography| south
On 03/20/2013 04:11 PM, Michael Orlitzky wrote:
On 03/20/2013 06:40 PM, Adrian Klaver wrote:
On 03/20/2013 03:26 PM, Michael Orlitzky wrote:
On 03/20/2013 05:18 PM, Rob Sargent wrote:
At the moment, everyone's just experimenting. Even with the proper
tooling, my blog app shouldn't have to h
Hi,
I have installed the PG on Linux RHEL 6.3. With the DATA directory stored on a
NAS Storage device which is NFS mounted on the main Linux disk.
What I am seeing is that after 40-60 minutes of PG running (in the product),
the 'postgres' dumps core and PG Services stops.
Has anyone seen this
On Wed, Mar 20, 2013 at 7:38 PM, W. Matthew Wilson wrote:
> I got this table right now:
>
> select * from market_segment_dimension_values ;
> +--+---+
> | market_segment_dimension | value |
> +--+---+
> | geography
On Wed, Mar 20, 2013 at 5:38 PM, W. Matthew Wilson wrote:
> I got this table right now:
>
> select * from market_segment_dimension_values ;
> +--+---+
> | market_segment_dimension | value |
> +--+---+
> | geography
On 3/20/2013 5:29 PM, Arvind Sharma wrote:
I have installed the PG on Linux RHEL 6.3. With the DATA directory
stored on a NAS Storage device which is NFS mounted on the main Linux
disk.
What I am seeing is that after 40-60 minutes of PG running (in the
product), the 'postgres' dumps core and
Wow, this is a fun puzzle. I'd love to be the first to solve it with
just SQL, but I don't have a solution yet. Here are some elements that
might be useful:
SELECT market_segment_dimension, array_agg(value)
FROM market_segment_dimension_values
GROUP BY market_segment_dimension;
the UNNEST functio
On 03/20/2013 08:05 PM, Adrian Klaver wrote:
>>
>> Now everything in the database will be owned by dev_user. But what
>> happens if we have 100 databases (this is realistic for us), and add a
>> new developer a year down the road? I have to not only add him to
>> dev_user, but look through each dat
Okay, how about this (table names shortened):
create table m (d varchar(255) not null, v varchar(255) not null);
insert into m (d, v) values ('geography', 'north'), ('geography',
'south'), ('industry type', 'retail'), ('industry type',
'manufacturing'), ('industry type', 'wholesale');
WITH RECURS
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 03/19/2013 09:46 PM, Stephen Frost wrote:
> * Craig Ringer (cr...@2ndquadrant.com) wrote:
>> As far as I'm concerned that's the immediate problem fixed. It may be
>> worth adding a warning on startup if we find non-self-signed certs in
>> root.crt
51 matches
Mail list logo