How to do performant insert if not exist in hibernate with postgres?

Question

I'm currently designing an image tag system. There's a n..m relationship from image to tag. The tag itself is restricted with a unique constraint.
Now let's say the user uploads an image with tags [animal,magic,summer]. The tag might exist in the database already, or might not exist. If it doesn't, it must be inserted. Now I cannot make a select and insert the missing tags, because two threads might to that in parallel, and one upload will fail due to unique constraint violation.

My current design works, but seems very unperformant (one insert per tag). I have to use a new transaction for these inserts due to postgres restrictions of type ERROR: current transaction is aborted, commands ignored until end of transaction block. Is there any better way?

    @Override
    @Transactional
    public String createImageEntry(MyDto myDto, long accountId) {
        for (String tag : myDto.getTags()) {
            insertTags(tag);
        }

        Set<ImageTag> tags = myDto.getTags().stream().map(s -> imageTagRepository.findByTag(s).orElseThrow()).collect(Collectors.toSet());

        Image image = new Image(...);
        imageRepository.persist(image);
        return image.getExternalKey();
    }

    @Transactional(Transactional.TxType.REQUIRES_NEW)
    public void insertTags(String tag) {
        ImageTag imageTag = new ImageTag();
        imageTag.setTag(tag);
        try {
            imageTagRepository.persistAndFlush(imageTag);
        } catch (PersistenceException ignored) {
        }
    }

@BoristheSpider which will still be a try to insert for every request's tag. Even though most likely they will exist already. But at the same time, Select, if not exists insert will fail if someone inserts in parallel too. And row lock isn't possible since there's no row to lock. Also hibernate doesnt support ON CONFLICT DO NOTHING :( — Regyn, Mar 27 '21 at 21:25

score 0 · Accepted Answer · answered Mar 29 '21 at 09:43

0

You can use @SQLInsert to change the SQL used for the insert statement. See the following answer for more details: Hibernate Transactions and Concurrency Using attachDirty (saveOrUpdate)

answered Mar 29 '21 at 09:43

Christian Beikov

6,925
1
26
48

I guess that's the way. A bit annoying because the column ordering is unpredictable, so each change is a pain – Regyn Mar 30 '21 at 20:21

How to do performant insert if not exist in hibernate with postgres?

1 Answers1