Обсуждение: Re: Insert into partition table hangs

"Nik" <XLPizza@gmail.com> wrote
> I have an application that executes an INSERT statement every second or
> two. In my old design this data was inserted into one table. However, I
> changed the design to have one table with multiple partitions. Now when
> I run the application it runs for some random amount of time (random
> number of INSERTs) and then it gets stuck at the INSERT query and it
> never finishes (at least not in some reasonable time - aka few hours).
> If I restart the application it continues inserting the data normally
> for a while before getting stuck again.
>

Do you do INSERT concurrently? Can you post a self-contained example to
reproduce it?

Regards,
Qingqing

I do not run the INSERTs concurrently. They are being executed as the
data comes in sequentially. I attempted to create a stand-alone example
to reproduce the issue, but I was not successful. I will post the
example anyway. This example ran fine, and did not hang at any point,
but it might give a good idea of the actual application mechanics.

-- Database objects
------------------------------

CREATE TABLE schemaname.master_table
(
   id int4 NOT NULL,
   speed float4 NOT NULL,
   "start" timestamptz NOT NULL
) WITHOUT OIDS TABLESPACE tbsname;
ALTER TABLE schemaname.master_table OWNER TO <username>;

CREATE TABLE schemaname.partition_table
(
  CHECK("start" >= '05-01-2006 00:00:00' AND "start" <= '05-31-2006
23:59:59'),
  CONSTRAINT partition_pk PRIMARY KEY (id, speed, "start") USING INDEX
TABLESPACE tbsname
) INHERITS (schemaname.master_table)
WITHOUT OIDS TABLESPACE tbsname;
ALTER TABLE schemaname.partition_table OWNER TO <username>;

CREATE OR REPLACE RULE master_table_insert AS
ON INSERT TO schemaname.master_table WHERE ("start" >= '05-01-2006
00:00:00' AND "start" <= '05-31-2006 23:59:59')
DO INSTEAD
INSERT INTO schemaname.partition_table VALUES
(
    NEW.id,
    NEW.speed,
    NEW."start"
);

-- Java application that inserts data
---------------------------------------------------

import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.TimeZone;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Main
{
    public static void main(String[] args)
    {
        System.out.println("Starting the application");

        Statement stmt=null;
        Connection conn=null;
        String query="";
        Long tstamp1=new Long("1148328357");

        try
        {
            Class.forName("org.postgresql.Driver");
            conn=DriverManager.getConnection("jdbc:postgresql://<ipaddress>/<dbname>",
"<username>","<password>");
            stmt=conn.createStatement();
            while(true)
            {
                tstamp1++;
                String tstamp2=convertTimestamp(tstamp1);

                query="INSERT INTO schemaname.master_table VALUES
(1,53.5,'"+tstamp2+"')";

                System.out.println("Inserting "+tstamp2+" ...");
                stmt.execute(query);
                System.out.println("DONE.");
            }
        }
        catch(SQLException e1)
        {
            System.out.println(e1.toString());
        }
        catch(ClassNotFoundException e2)
        {
            System.out.println(e2.toString());
        }
    }

    // Convert the time in seconds from epoch to the timestamptz format
    public static String convertTimestamp(Long timeIn)
    {
        if(timeIn!=null)
        {
            // Generate a date filetype by converting the seconds to
milliseconds from epoch
            Date d = new Date((timeIn.longValue()*1000));

            // Specify the date format
            SimpleDateFormat df = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");

            // Get the date into a string
            String partDate = df.format(d);

            // Get the current timezone
            TimeZone tz = TimeZone.getDefault();

            // Adjust the timezone offset
            int dstOffset =
((((tz.getRawOffset()+tz.getDSTSavings())/1000)/60)/60);

            // Add the seconds to the date
            String result = partDate+" "+dstOffset+":00";

            return result;
        }
        else
            return "";
    }
}

On Mon, May 22, 2006 at 01:38:20PM -0700, Nik wrote:
> CREATE TABLE schemaname.partition_table
> (
>   CHECK("start" >= '05-01-2006 00:00:00' AND "start" <= '05-31-2006
> 23:59:59'),

What about 5-31-2006 23:59:59.3?

What you really want is:
   CHECK("start" >= '05-01-2006' AND "start" < '06-01-2006')

PostgreSQL will assume midnight, so you don't actually need the time.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Ok, that's a good point. I overlooked the possibility of this
considering that I didn't think the timestamp resolution was that high.
I changed my rules and constraints on tables to take care of this case.
Thanks.

However, how does this affect the problem I having considering that
inserts that were getting stuck were not necessarily close to critical
time (midnight)?

Also, today a developer reported similar issue when running a select
statement from this partitioned table. He said that query would just
run forever and if he stopped it and ran it again, it would return data
within seconds. It was behaving in this manner randomly (aka it would
work a few times in a row, then it would get stuck).

As before I am not seeing any locks or unusal behavior in the logs and
pg_stat_activity.

I thought that since application continues running fine after I restart
it, I would try creating a separate connection for each insert
statement, and then disconnect when it's completed.

However, this had the same problem. It would connect, insert, and
disconnect several hundred times, and then at random point it would
connect, and insert would get stuck again.

I am running out of ideas on how to fix this issue. I had a suggestion
to go back to non-partitioned tables with partitioned index, but I
would prefer not to do this. Is there anything else I can do to fix
this problem?

Thanks.

I am still having the same problem. I tried to eliminate any possible
network issues by placing the application on the server itself, but the
same thing happened.

However, I had an interesting discovery today that I don't understand.

I left the insert query that was stuck running. Then, without changing
any parameters, I ran reload configuration, aka. sending SIGHUP, and
the query immediately completed and went on to the next insert.

I also noticed that I am having the similar issue with SELECT
statements. Same SELECT statement will run fine 3-4 times in a row and
then get stuck and run forever with no results. But if I reload the
configuration like above, the query immediatelly returns with correct
result.

Why does this happen, and could this give any insight in my problem?

Thanks.

"Nik" <XLPizza@gmail.com> wrote
>
> I also noticed that I am having the similar issue with SELECT
> statements. Same SELECT statement will run fine 3-4 times in a row and
> then get stuck and run forever with no results. But if I reload the
> configuration like above, the query immediatelly returns with correct
> result.
>
> Why does this happen, and could this give any insight in my problem?
>

Interesting enough :-(

I can see that for some reason, the query may be blocked on a semaphore and
also listen to the signal. So when the SIGHUP comes, it will wake up and set
a variable "got_SIGHUP" to true (that's the only immediate thing do for
SIGHUP). After that, we either continue to sleep on the semaphore or exit
with a FATAL message -- both situations does not match your senario.

A wild guess is that when we continue to sleep on the semaphore, magically
we get it, but how come?

Regards,
Qingqing

"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
> A wild guess is that when we continue to sleep on the semaphore, magically
> we get it, but how come?

Buggy SysV semaphore support in the kernel, maybe?  What platform is
this exactly?

            regards, tom lane

""Nikola Ivanov"" <xlpizza@gmail.com> wrote
> Database is running on Windows 2003 Advanced Server and it is PostgreSQL
> 8.1.3.
>
> On 6/9/06, Tom Lane <tgl@sss.pgh.pa.us> wrote
> >
> > Buggy SysV semaphore support in the kernel, maybe?  What platform is
> > this exactly?
> >

Yes, there is a bug in 8.1.3 Win32 SYSV semaphore emulation but I can't
figure out how the bug will lead this behavior ...

There is patch applied to the CVS tip -- you can find it in the patches
(somthing like "Win32 semaphore patch"). If you can, try to apply it to your
server (8.0.*, 8.1.* are all ok with it). There was one successful case of
solving the semaphore problem with the patch.

Regards,
Qingqing