Re: [Mojolicious] SQL::Abstract::Pg

On Sun, Feb 4, 2018 at 8:39 AM, sri wrote: > > Almost. You do not want more than one active statement handle per > connection. The while loop > is pointless there anyway, because DBD::Pg does not support cursors. So > all results will be sent > to DBD::Pg as soon as you call $results->hash for the

Re: [Mojolicious] SQL::Abstract::Pg

> > Thanks for the feedback! Is this a correct implementation of what you're > saying? > > my $db = $pg->db; > my $results = $db->select('a', [qw/first last birthday age phone/], undef, > {limit => $limit, offset => 1}); > my $tx = $db->begin; > while ( my $next = $results->hash ) { > $db->in

Re: [Mojolicious] SQL::Abstract::Pg

On Sun, Feb 4, 2018 at 4:04 AM, sri wrote: > > There's a lot wrong with this. You should hold on to $db objects, there's > a pool of connections in > Mojo::Pg. Manual prepare/execute is pointless, Mojo::Pg::Database has a > transparent statement > handle cache, once you hold on to the $db object y

Re: [Mojolicious] SQL::Abstract::Pg

> > sub bulk_insert { > > my ($self, $table, $records, $options) = @_; > my ($stmt) = $self->pg->abstract->insert($table, $records->[0], > $options); > eval { > my $tx = $self->pg->db->begin; > my $i = $self->pg->db->dbh->prepare($stmt); > while ( my $next = shift @$records ) {

Re: [Mojolicious] SQL::Abstract::Pg

On Sun, Feb 4, 2018 at 1:16 AM, Stefan Adams wrote: > as Sebastian said at the beginning, it's really all that useful for > Mojo::Pg. > My use case for it is that I'm building a web service cache. We use a SaaS ERP-type database that provides a SOAP API that's super slow. The API documentation

Re: [Mojolicious] SQL::Abstract::Pg

Transactions provide insane bulk insert performance: *It is of note here that each insert is a transaction. What this means is Postgres is doing some extra coordination to make sure the transaction is completed before returning. On every single write this takes some overhead. Instead of single row

Re: [Mojolicious] SQL::Abstract::Pg

> > For example, we can buffer minion jobs via enqueue and insert them to > minion_jobs all at once in the end of request. > > That feature also is very valuable for highload project if you want to > store some kind of logs in the database. > > So bulk inserts is definitely a good thing and I us

Re: [Mojolicious] SQL::Abstract::Pg

I think, the case is not about inserting bulk data from one table to another. For example, we can buffer minion jobs via enqueue and insert them to minion_jobs all at once in the end of request. That feature also is very valuable for highload project if you want to store some kind of logs in t

Re: [Mojolicious] SQL::Abstract::Pg

On Fri, Feb 2, 2018 at 10:16 PM, Stefan Adams wrote: > > > On Thu, Feb 1, 2018 at 6:42 AM, Sebastian Riedel wrote: >> >> > I spoke with you about this briefly on Twitter, but just figured I'd put >> > it >> > out here, too. What about multiple record inserts on a single call? >> > Twitter thread

Re: [Mojolicious] SQL::Abstract::Pg

A bulk insert would look more like: my $rows = $pg->db->select(...)->arrays; $pg->db->query('insert into b (first, last, birthday, age, phone) values ' . join(',', ('(?,?,?,?,?)')x@$results), @$results); Or with postgres you could probably use arrays rather than constructing the query with join an

Re: [Mojolicious] SQL::Abstract::Pg

On Thu, Feb 1, 2018 at 6:42 AM, Sebastian Riedel wrote: > > I spoke with you about this briefly on Twitter, but just figured I'd put > it > > out here, too. What about multiple record inserts on a single call? > > Twitter thread. > > Yes, it's possible, not sure about how useful it would actuall

Re: [Mojolicious] SQL::Abstract::Pg

> I spoke with you about this briefly on Twitter, but just figured I'd put it > out here, too. What about multiple record inserts on a single call? > Twitter thread. Yes, it's possible, not sure about how useful it would actually be though. -- Sebastian Riedel http://mojolicio.us http://github.

Re: [Mojolicious] SQL::Abstract::Pg

I spoke with you about this briefly on Twitter, but just figured I'd put it out here, too. What about multiple record inserts on a single call? Twitter thread. On Sun, Jan 28, 2018 at 4:23 PM, sri wrote: > Just wanted to give you a quick he